SQL SERVER – DATE and TIME in SQL Server 2008

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.

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.

I have tried your code in both sql server 2005 and sql express 2008,but i have not get any result instead i got the following error message “Msg 2715, Level 16, State 3, Line 6 Column, parameter, or variable #1: Cannot find data type DATE. Parameter or variable ‘@varDate’ has an invalid data type. Msg 2715, Level 16, State 3, Line 6 Column, parameter, or variable #2: Cannot find data type TIME. Parameter or variable ‘@varTime’ has an invalid data type.”

When a time is converted to datetime, by default the base date 1900-01-01 added to it until you specify the date Also refer this to knwo the basic understanding on how datetime columns work in SQL Server

Yes the TIME datatype is new to sql server 2008. I would excersise caution trying to run scripts using new datatypes in 2008 on 2005. Probably one way would is to use common data types betwen 2005 and 2008. In other words if one is developing on both platforms go with the common denominator features.

im new to sql.and i have recently installed sql server 2008 and when i tried to craete a new table i got the following error like cannnot find the data type number for the below query..tell me how to overcome this.. thanks in advance…the query and the error are given below… CREATE TABLE department( departmentid NUMBER(2) CONSTRAINT pk_department_departmentid PRIMARY KEY, departmentname VARCHAR2(30) NOT NULL, headofdepartment VARCHAR2(4) );

Hi, I have 2 columns in database one column for inserting only hours and another column for inserting present datetime format. In this how can i get the difference between these two columns and display the value. For example i am inserting 30 hrs for hours column and inserting present date. If am opening the page in after 2 hrs .remainig time is 28 hrs. How can i get this value from these two columns difference. can you please help me

i have history data table in which date and time column i want data from datewise but in time 8am to todays date and next 8am to next day.. in shot just like 12 am to 12am i want 8 am to 8am .. can u please hlep me?

Question: I am running the same Delphi 2007 code on two Windows 7 machines using an SQL server database on a remote server. When I run the code one one machine it crashes on the following line: aQuery.FieldByName(‘curr_date’).asDateTime but on the other machine the code runs without any issues. The ‘curr_date’ field is a Datetime2 datatype. Found that if I check the value of the ‘curr_date’ as a string (i.e. aQuery.FieldByName(‘curr_date’).asString), on one machine the date is formatted as ‘1/25/2013’ and on the other machine the date is formatted as ‘2013-1-25 00:00:00.000’. The machine on which the date is formatted as ‘2013-1-25 00:00:00.000’ is the machine on which the code crashes. The regional settings in Control Panel are identical on both machines. Anyone know what the problem is?

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.