Just how to l k for Date and Time Values
by Bryan Syverson, composer of Murach’s SQL for SQL Server
Suppose you’re composing a question to locate all of the invoices that have been written on 6, 2003 january. You understand through the control totals that 122 invoices were written that day. But when this query is run by you
The outcome set is empty. What are you doing?
How dates and times are saved in SQL Server
Before you can effortlessly query date/time (or temporal) data, you must know one thing about how precisely date/time values are kept. SQL Server supports two date/time data types datetime and smalldatetime. The difference between the two is the number of storage used. Datetime makes use of 8 bytes of storage space, while smalldatetime uses just 4 bytes. For this reason, datetime can represent date/time values within a wider range sufficient reason for more precision than smalldatetime. These distinctions are summarized in the dining table below.
Both datetime and smalldatetime represent the date and time as a value that is add up to the true number of days in relationship to a base date. That base date is midnight on January 1, 1900 in SQL Server. The smalldatetime type can only represent dates from this base date on as you can see in the table. Continue reading