T-SQL's Datetime Data Type

SQL Server's datetime data type generates a lot of questions and confusion in the SQL Server community. Unlike some other major database platforms, SQL Server doesn't provide discrete date and time data types. Instead, SQL Server's datetime data type does the work of both. Here are answers to six commonly asked questions about how to use SQL Server's datetime data type. (See also, "Inside Datetime Data" and "Help for the DATETIME data type and DST on SQL Server").

6. How does SQL Server store the datetime data type?

SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

5. How do I retrieve rows based on datetime values?

SQL Server recognizes date and time data enclosed in single quotes. You can couple date and time values together or use them independently. You can also combine character date formats ('May 15, 2004 4 am'), numeric date formats ('5/15/2004 04:30'), or contiguous string formats ('20040515') with standard , or = operators, as the following example shows:

SELECT * FROM orders WHERE OrderDate

4. How do I retrieve only the date or time portion of the data?

You can use T-SQL's DATEPART() function to return a subset of the values that SQL Server's datetime columns store. The DATEPART() function uses two arguments. The first argument specifies the portion of the date that you want, and the second value specifies the datetime column:

SELECT orderID, DATEPART(MM,OrderDate) AS OrderMonth FROM Orders

3. How do I insert a value into a datetime column?

To insert values into a datetime column, you need to enclose the values in single quotes, then use one of SQL Server's date formats to supply the date value that you want to insert. For example:

Using the weekday argument as its first parameter, SQL Server's DATEPART() function returns the day of the week, returning 1 for Sunday, 2 for Monday, and so on. The following example uses the GETDATE() function combined with the DATEPART() function to retrieve the current day value:

SELECT DATEPART(weekday, GETDATE())

1. How can I find the last day of the month?

You can combine T-SQL's DATEADD() and DATEDIFF() functions to calculate different date and time values. Subtract 5ms from the first day of the next month to find the last day of the current month:

Discuss this Article 13

Diana (not verified)

on Jan 25, 2007

Date Formats
I was disappointed to read an IT pro recommending anything other than ISO/ODBC date formats in a select statement, in Michael Otey's Select Top(X): "T-SQL's Datetime Data Type" (September 2004, InstantDoc ID 43488). Americans probably don't see what the big deal is with writing dates "backwards" (mm/dd/yyyy). But there is a world outside the United States, and the rest of the world writes dates in the more logical format of dd/mm/yyyy or yy/mm/ dd. More logical because the order of the significance of the parts either increases (dd/ mm/yyyy, as in UK format) or decreases (yy/mm/dd, as in Korean format). The illogical US format mixes the significance of the parts and is akin to writing a time of 30 minutes and 45 seconds past 10:00 in the morning as 10:45:30 or 45:10:30. I was disappointed that Mr. Otey indirectly encouraged the representation of dates as "May 15, 2004 4 am," "5/15/2004 04:30," and "May 15, 2004." You might not see the significance of this, but in my opinion it's irresponsible of you to publish, in a global resource, anything other than unambiguous format.
—Daniel Clarke

WOW! I have to say I like how the article states "SQL Server's datetime data type does the work of both" In IBM DB2 it's a simple as DATE(timestamp_col) to return the date (assuming it was a timestamp field and not a plain Date field). I love SQL Server but also work with DB2 and it always seems that the SQL Server priests always make it seem like SQL Server's way is the best, even when it plainly isn't. Having a Date, Time, and Timestamp data types in DB2 gives me flexibility that I wish SQL Server had in an easier fashion.

Totalyy un usefull. Dose not solve any of the problems people usually face. Mainly how: 1)to get the date portion of a datetime; 2) how to get the time portion and the difference with smalldatetime 3)what is timestamp
1)
// replace getdate() with the datetime column,expresion your intrested in.
DONT'T do cast(convert(varchar(20),getdate(),110) as datetime) or cast(convert(varchar(20),getdate(),114) as datetime) as it returns varchar that will have to be converted back to datetime. Much more CPU (text parsing).
DO USE:
Date: cast( datediff( day, getdate(), 0) as datetime )
Time: getdate() - datediff(day, getdate(), 0)
2) datetime vs smalldatetime: datetime larger timespan year span of 1753 to 9999 and a resolution of 1/300 of seconds (0.00333 sec). Where as smalldatetime 1900 to 2079 with a presition of 1sec. Also 8 vs 4 bytes respectivly.
3) I all mostforgot timestamp. Actually not a date it's used something like a guid. If you really what to get a timestamp use getdate() as a default value for the column.

4. How do I retrieve only the date or time portion of the data?
SELECT orderID, DATEPART(MM,OrderDate) AS OrderMonth FROM Orders
How exactly does this query return the date portion of a datetime field type? Perhaps you meant something like this?
CONVERT(Char, tCreateTime, 101)
or
CAST(CONVERT(Char, tCreateTime, 101) AS DateTime)
?
Dan

It would be more useful if it wasn't wrong. The statement, "The second 4 bytes are an integer value that represents the number of milliseconds since midnight," merely echoes the BOL, which is likewise incorrect. The low order 4 bytes actually store the number of 3.33 ms intervals since midnight.
This T-SQL statement proves it:
select (3.33 * (convert(int,
substring(convert(binary(8), getdate()), 5, 4)
))) / 3600000.0 As hours

When I inserted records containing date fields that were null, SQL Server put in 1/1/1900. Then, of course, the successor application that pulled up the records found 1/1/1900 in these fields. Is this SQL Server’s only way of expressing null in date fields, which presumably I would test using a T-SQL ISNULL expression, or for the literal value, if I were testing for it in Visual Basic?

"When I inserted records containing date fields that were null, SQL Server put in 1/1/1900."
SQL server WILL insert a NULL value if you tell it to. If you are seeing 1/1/1900 it is probably from the application side. That is VB.NET datetime value when set to nothing.

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More