Suppose we need to search all messages by a given date from a Database table, named ‘Message’ we might write the query as given below:
SELECT * FROM messages
WHERE Date = ‘2003-01-06’
But we shouldn’t be surprised at being returned an empty set because we have got the fundamentals wrong about how dates are stored in database.
‘Datetime’ can represent dates within a range from “Jan 1, 1753 midnight” to “Dec 31, 9999 23:59:59.997 (0.003 seconds until midnight) ”. SQL Server stores date time as a real number and is calculated on the basis of how much time has elapsed from the base date time.
Note:In SQL Server, base datetime is January 1, 1900 midnight. ‘datetime’ can represent date-times before it(within the range), but in negative form.
SELECT CAST(CAST(‘1900-01-04 08:00’ AS datetime) AS float)
The above query will give us the result 3.33333333333333. This behaviour can be explained properly with the fact that it is 3 day after the base date and by 8:00AM a third the day has elapsed, so the result is “3.33333333333333” as a whole.