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:

DECLARE @MyTable TABLE (MyDateTime DATETIME)INSERT INTO @MyTable VALUES ('May   15, 2004 11:25am')

2. How do I find the day of the week?

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:

SELECT DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE()  )+1, 0))