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.

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


SQL SERVER - DATE and TIME in SQL Server 2008 convertdatetime

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – SQL Server Technology Evangelists and Evangelism
Next Post
SQLAuthority News – Guest Post – Performance Counters Gathering using Powershell

Related Posts

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.”

    Reply
  • 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

    Reply
  • 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.

    Reply
  • can i use these changes in sql server 2008 express edition

    Reply
  • 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

    Reply
  • How we can convert Datetime to Date Only ….

    Reply
  • @ rahul Kumar …. to convert sql data and time data type into date

    syntax :-

    convert(DATE, convert(VARCHAR, TableName.ColumnName, 101))

    Reply
  • 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”

    Reply
  • 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)
    );

    Reply
  • 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.

    Reply
  • Hi Pinal
    How can we find that which trigger is created on which table..?
    Is there any script for this..?

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • 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*/

    Reply
  • 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?

    Reply

Leave a Reply

Menu