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
Here is the video discussing the same concepts.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Hi Pinal,
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.”
Hi,
It will only work in SQL Server 2008 and not in 2005.
Kind Regards,
Pinal
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 http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
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.
[...] SQL SERVER – The Difference between Dual Core vs. Core 2 Duo SQLAuthority News – Wireless Router Security and Attached Devices – Complex Password SQL SERVER – DATE and TIME in SQL Server 2008 [...]
can i use these changes in sql server 2008 express edition
Dear Pinal,
how can I disable constraints and triggers of a table in SQL database just like in oracle database to insert some data?
Thanks,
Jacob
Alter table table_name disable contraint contraint_name
How we can convert Datetime to Date Only ….
@ rahul Kumar …. to convert sql data and time data type into date
syntax :-
convert(DATE, convert(VARCHAR, TableName.ColumnName, 101))
Select Convert(Varchar(10),getdate(),105)
[...] SQL Server Interview Questions and Answers ISBN: 1466405643 Page#133 Difference Between DATETIME and DATETIME2 Difference Between GETDATE and SYSDATETIME Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime Datetime Function TODATETIMEOFFSET Example Information Related to DATETIME and DATETIME2 Datetime Function SWITCHOFFSET Example Difference Between DATETIME and DATETIME2 – WITH GETDATE DATE and TIME in SQL Server 2008 [...]
Dear pinaldave
please help me
i want the difference between time
i have two column with the datetime data type
i tried the following query but it is not working
“SELECT CONVERT(VARCHAR(5),OutTimeEnter,108) – CONVERT(VARCHAR(5),InTimeEnter,108)
FROM dbo.trx_DailyWork ”
I want the total time between “InTime” and “OutTime”
hi pinal ,
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)
);
the error msg for the above query was this..
:Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type NUMBER.
NUMBER is not a valid datatype in SQL Server. You should use INT
[...] DATE and TIME in SQL Server 2008 [...]
Hi Pinal
How can we find that which trigger is created on which table..?
Is there any script for this..?
Query on sys.triggers
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
[...] DATE and TIME in SQL Server 2008 [...]
hello sir , i have changed database from mssql 2003 to mssql 2008 and having problem on converting varchar to datetime.this Code work fine in mssql 2003.Im confuse.Please help me. I cnt find the error.
Please refer below.
mtg_date = Format(CDate(Me.txtmtgdate.Text & ” ” & Me.ddlHourFrom.SelectedValue & “:” & Me.ddlMinFrom.SelectedValue & Me.ddlTimeFrom.SelectedValue), “yyyy-MM-dd hh:mm:ss”)
input data=12 DEC 2012 5 00 pm
* This coding not do any changes on datetime format.Please guide me.
Thank You.
hi pinal sir
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?