SQL SERVER – DATE and TIME in SQL Server 2008

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Thu, 27 May 2010 01:30:38 +0000 Indexed on 2010/05/27 1:33 UTC
Read the original article Hit count: 907

I was thinking about DATE and TIME datatypes in SQL Server 2008. I earlier wrote about the about best practices of the same. Recently I had written one of the script written for SQL Server 2008 had to run on SQL Server 2005 (don’t ask me why!), I had to convert the DATE and TIME datatypes to DATETIME. Let me run quick demo for the same.

DECLARE @varDate AS DATE
DECLARE @varTime AS TIME
SET @varDate = '10/10/2010'
SET @varTime = '12:12:12'
SELECT CAST(@varDate AS DATETIME) C_Date
SELECT CAST(@varTime AS DATETIME) C_Time

As seen in example when DATE is converted to DATETIME it adds the of midnight. When TIME is converted to DATETIME it adds the date of 1900 and it is something one wants to consider if you are going to run script from SQL Server 2008 to earlier version with CONVERT.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


Filed under: SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about sql

Related posts about SQL Authority