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

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)

About these ads

26 thoughts on “SQL SERVER – DATE and TIME in SQL Server 2008

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

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

  3. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

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

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

    syntax :-

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

  6. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

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

  8. 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)
    );

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

  10. Pingback: SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video « SQL Server Journey with SQL Authority

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

  12. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

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

  14. 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?

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  16. 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*/

  17. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s