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.
Quick Video on the same subject
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]Here is the video discussing the same concepts.
[youtube=https://www.youtube.com/watch?v=61pHO5pZ1o4]Reference: Pinal Dave (https://blog.sqlauthority.com)
