What is DATETIME and how do I work with it?

To understand and work with datetime datatype values, you must first understand that there is a difference in how dates and times are STORED and how they are DISPLAYED by SQL Server.

How they are stored.

The following is an extract from the BOL.....

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Click to expand...

In short, date and times are NOT stored in any 'recognizable' format.

How they are displayed.

When you DISPLAY the date and/or time, you are CONVERTing the integer value into a recognizable format. This can be done two ways.

1. Default. SQL Server will DISPLAY the date and time values based on the COLLATION/LANGUAGE setting of your SQL Server.

2. CONVERT. Using the CONVERT command (refer to the BOL), you can display the date and/or time in different formats.

Examples:

1. First you set the datatype for the date/time to be converted to, in this case I chose VARCHAR. You must set the datatype large enough to hold the full value you want returned. More on that later.

2. Next, you enter the datetime field or value to be converted. This can be a column name, an actual datetime value ('03/15/2005'), or the current datetime (GETDATE()). Or as Rudy (handle R937) words it "...any expression, no matter how complex, that results in a datetime value."

3. Lastly, enter the STYLE value. This is what sets the format for the displayed date/time. Note: 120 and 20 return almost the same format. The only difference is 120 is yyyy and 20 is yy. This is the same with ALL style values. (run the above command with 20 instead of 120).

Reviewing the CONVERT description in BOL, you will find the chart that lists all the STYLE values. You will see that some of them are DATE only and two are time only. If no date AND time style matches what you need, you can combine the DATE only with the TIME only:
Run at 2005-04-25 18:30:15, the above command will return:

Earlier, I said that when using CONVERT.. "you must set the datatype large enough to hold the full value you want returned". In the examples, we have returned the date and time. Let's say you only want the date and the hour returned. Well, there's no STYLE for that. The solution is to chose the format you want the values returned in and only return the values you want. Run both of the following commands. They are the same command, except the second one returns less characters.


That is basically everything you will normally need to know about DATETIME values. To add a bit of confusion, SQL Server has something called TIMESTAMP.

This is a quote from the BOL:

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

Click to expand...

So remember that, in SQL Server, TIMESTAMP has nothing to do with the date/time.

I hope I haven't totally confused you and that this FAQ gives you a better understanding of DATETIME value and how to work with it.

-SQLBill

Reference Material:
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Thanks and acknowledgements:
To Rudy (R937) - for providing better wording on what type of value can be converted.