WinSQL » Knowledge base

Document information

Document ID:	779
Subject:	Formatting date fields in resultset
Creation date:	7/29/09 3:21 PM
Last modified on:	7/29/09 3:21 PM

Details

When you run any query that returns a date field, both WinSQL and JaySQL return data in the following format.

YYYY-MM-DD hh:mm:ss.ms

WinSQL displays data exactly the way it is returned by the ODBC or the JDBC driver. Whenever date, datetime or numeric fields are involved in a resultset, the driver converts the result into string. Since this conversion is done by the driver and not WinSQL/JaySQL, there is no setting within the program that changes the format.

However, there is a work-around. If you can convert the date or numeric field on the server end, the ODBC/JDBC driver will treat this value as a string and won't change its format.

Consider the following query that is run against a Microsoft SQL Server.

select dateOfBirth, CONVERT(varchar(20), dateOfBirth, 101) as FormattedDOB
from Student
where dateOfBirth > '7/1/1980'

The above query returns two columns containing same result in different format. In case of the second column the date is converted to string on the server end and therefore, the driver returns it as-is.

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip