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)
21 Comments. Leave new
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.”
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.
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)
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
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
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?
Dear Mr Pinal Dave,
I have a looping query. My question is, Is it possible to make this looping to nested query for efficiency. Thanks…
I attached the query.
DECLARE @MAX INT
DECLARE @MIN INT
DECLARE @MAX_NIK INT
DECLARE @MIN_NIK INT
/*TEMP 1*/
DECLARE @Data TABLE
(
ID INT IDENTITY(1,1),
TGL varchar(10)
)
DECLARE @TGL varchar(10)
/*TEMP 2*/
DECLARE @DATANIK TABLE
(
ID_NIK INT IDENTITY(1,1),
NIK INT
)
DECLARE @NIK INT
/*INSERT DATA TGL*/
INSERT INTO @Data
SELECT DISTINCT(CONVERT(VARCHAR(10),DATE,103)) FROM T_TEST
SET @MIN = 1
SET @MAX = (SELECT MAX(ID) FROM @Data)
–SELECT * FROM @Data
WHILE @MIN <= @MAX
BEGIN
SELECT @TGL = TGL FROM @Data WHERE ID=@MIN
INSERT INTO @DATANIK
SELECT DISTINCT(ID) AS NIK FROM T_TEST
SET @MIN_NIK = 1
SET @MAX_NIK = (SELECT MAX(ID_NIK) FROM @DATANIK)
WHILE @MIN_NIK <= @MAX_NIK
BEGIN
SELECT @NIK = NIK FROM @DATANIK WHERE ID_NIK = @MIN_NIK
SELECT DISTINCT ID,MAX(DATE) AS CHECKOUT,MIN(DATE) AS CHECKIN FROM T_TEST
WHERE ID = @NIK AND DATEDIFF(D,CONVERT(Datetime, DATE, 103),CONVERT(Datetime, @TGL, 103)) = 0
GROUP BY ID
SET @MIN_NIK = @MIN_NIK + 1
END /*END WHILE DATANIK*/
SET @MIN = @MIN + 1
END /*END WHILE DATA*/
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?