SQLTeam.com | Weblogs | Forums

Date format


Hi All,

I am fetching records from one screen in DD/MM/YYY format and in another page i need to run a query based on date which are coming in from first page.

Now in database it is stored as MM/DD/YYYY

Please suggest


If you are storing the data as date datatype, you can always format it to the way you wanted. If you storing it as varchar, then you will need to split the string into the format you needed and use that to form your query.


Also, it may be displayed as MM/DD/YYYY, but it is saved in the DB as the number of ticks since 1901.


Not in SQL Server it's not. If it's the DateTime datatype, it's stored as two integers. The first is the number of days since midnight on the first of January 1900 and the time is stored as the number of 1/300ths of a second (that's the reason why the resolution of DATETIME is 3.3ms rounded to the nearest ms) since midnight of the given day. The "newer" datatypes are similar but with a finer resolution being possible. And, yes, negative days in the first integer are possible, which is how it stores dates prior to 1900.


What is the datatype of the column where these dates are stored?

Also, your question is terribly generic. You need to provide more detail or the answer will be "just do a proper temporally close/open join to ensure you encompass any changes that would someday include time and to make the query SARGable".