|Subject:||Formatting date fields in resultset|
|Creation date:||7/29/09 3:21 PM|
|Last modified on:||7/29/09 3:21 PM|
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.msWinSQL 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
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.