. Quick Links

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.

User comments

Posted by fuad on 4/30/10 2:55 PM

how to delet row form table

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? Please add it below. Your name and tip will appear at the end of the document text.

Your name:
Your email:	Hide my email address
Verification code:	Enter the verification code you see above more submitting your tip
Tip:	Please limit tips to 1000 characters