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.

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 < 'May 15, 2004'

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))

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

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

DanPFW,DanPFW- August 27, 2004

Article Rating 2 out of 5

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

mmcginty_SQL- September 01, 2004

Article Rating 2 out of 5

"Day of the week". The value returned depends on the "datefirst" setting. SET DATEFIRST 1 will return 1 for monday.

Micke Schönning- September 03, 2004

Article Rating 2 out of 5

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?

curtdey- September 10, 2004

Article Rating 3 out of 5

"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.

GREG_BURNS- September 10, 2004

Article Rating 2 out of 5

Too much mistakes.

Matjaz_SQL- September 14, 2004

Article Rating 1 out of 5

select convert(datetime,floor(convert(decimal(18,6),getdate())))

Anonymous User- October 14, 2004

To convert datetime data to date only format test this code

select convert(datetime,floor(convert(decimal(18,6),getdate())))

Anonymous User- October 14, 2004

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.

mghale- June 27, 2006

Article Rating 3 out of 5

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.

jccondor- July 24, 2006

Article Rating 1 out of 5

I didn't find options with negative numbers for rating this article ...

ulisescab- August 31, 2006

Article Rating 1 out of 5

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

DianaMay- January 25, 2007

Article Rating 3 out of 5

 
 

ADS BY GOOGLE