Twitter Updates

    follow me on Twitter

    Thursday, August 03, 2006

    Date and Time Manipulation in SQL Server 2000

    SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.

    SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight.

    So, the most common question that is asked is:

    Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?
    A: By using the Convert function. The syntax for using the convert function is:

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


    The list of styles that can be used are:

    Style ID Style Type
    0 or 100 mon dd yyyy hh:mi AM (or PM)
    101 mm/dd/yy
    103 dd/mm/yy
    105 dd-mm-yy
    106 dd mon yy
    107 Mon dd, yy
    108 hh:mm:ss
    9 or 109 mon dd yyyy hh:mi:ss:mmm AM (or PM)
    110 mm-dd-yy
    111 yy/mm/dd
    112 yymmdd
    13 or 113 dd mon yyyy hh:mm:ss:mmm (24h)
    114 hh:mi:ss:mmm (24h)
    20 or 120 yyyy-mm-dd hh:mi:ss (24h)
    21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
    126 yyyy-mm-dd Thh:mm:ss.mmm (no spaces)
    130 dd mon yyyy hh:mi:ss:mmm AM
    131 dd/mm/yy hh:mi:ss:mmm AM


    No comments: