Friday 2 March 2012

Sql Server Beginners : New Date and Time datatype in Sql Server 2008


Sql Serve 2008 introduce new Date and Time data types.
Here are the list of new datatypes:

SQL Server 2008 data type Default string literal format
time hh:mm:ss[.nnnnnnn]
date YYYY-MM-DD
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]
datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 

Here are Comparison between date and time datatypes.

Query :
SELECT
     CAST('2012-03-02 12:40:39. 1234567 +12:10' AS time(7)) AS 'time'
    ,CAST('2012-03-02 12:40:39. 1234567 +12:10' AS date) AS 'date'
    ,CAST('2012-03-02 12:40:39.123' AS smalldatetime) AS
        'smalldatetime'
    ,CAST('2012-03-02 12:40:39.123' AS datetime) AS 'datetime'
    ,CAST('2012-03-02 12:40:39. 1234567 +12:10' AS datetime2(7)) AS
        'datetime2'
    ,CAST('2012-03-02 12:40:39.1234567 +12:10' AS datetimeoffset(7)) AS
        'datetimeoffset';

Here are the output of the query:
SQL Server 2008 data type Output
time 12:40:39.1234567
date 2012-03-02
smalldatetime 2012-03-02 12:41:00
datetime 2012-03-02 12:40:39.123
datetime2 2012-03-02 12:40:39.1234567
datetimeoffset 2012-03-02 12:40:39.1234567 +12:10

No comments:

Post a Comment