Date Handling with SQL Server

Next Post
By Admin at 17 Mar 2010, 16:52 PM
  • DATETIME Column


    The date range for a DATETIME column can be January 1, 1753 to December 31, 9999. SQL Server uses 8 bytes to store the DATETIME data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1753. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

    SMALLDATETIME Column


    The date range for a SMALLDATETIME column can be January 1, 1900 to June 6, 2079. SQL Server uses 4 bytes of storage for SMALLDATETIME column. The first 2 bytes integer piece contains the number of days since January 1, 1900 and the second 2 byte integer holds the number of minutes since midnight.

    CONVERT (VARCHAR, Column, Style)


    We can display the date and time data in many different formats using the CONVERT function with different styles.

    Example:

    1. SELECT GETDATE ()

    Result:

    2009-10-26 13:39:59.543

    Example:

    1. SELECTCONVERT(VARCHAR, GETDATE(), 101)

    Result:

    10/26/2009

    Different style numbers result in different formats of date time displayed.

    StyleResult
    0 or 100Oct 26 2009 1:41PM
    110/26/2009
    209.10.26
    326/10/09
    426.10.09
    526-10-09
    626 Oct 09
    7Oct 26, 09
    8 or 24 or 10813:42:23
    9 or 109Oct 26 2009 1:42:42:543PM
    1010-26-09
    1109/10/26
    12091026
    1326 Oct 2009 13:46:14:590
    1413:46:25:967
    202009-10-26 13:46:51
    21 or 252009-10-26 13:48:29.340
    2210/26/09 1:48:53 PM
    232009-10-26
    2413:48:53
    10110/26/2009
    1022009.10.26
    10326/10/2009
    10426.10.2009
    10526-10-2009
    10626 Oct 2009
    107Oct 26, 2009
    11010-26-2009

    DATEPART:


    The DATEPART function returns the value of a specific date part from a date. Use this function to retrieve the months, hours, weekdays, etc. from a given date.

    Syntax:

    1. DATEPART (datepart, date)

    Example:

    1. select getdate()

    Result:

    2009-10-26 14:04:07.793

    Example:

    1. select DATEPART(dy, getdate())

    Result:

    299

    Example:

    1. <span id="dnn_ctr496_MainView_ViewEntry_lblEntry"><span><span class="keyword">select</span><span> DATEPART(m ,getdate()) </span></span></span>

    Result:

    10

    DATEADD:


    The DATEADD function returns a new datetime based on adding a specified date-part interval to a date.

    Syntax:

    1. DATEADD (datepart, number, date)

    Example:

    1. <span id="dnn_ctr496_MainView_ViewEntry_lblEntry"><span><span class="keyword">select</span><span> getdate() </span></span></span>

    Result:

    2009-10-26 14:04:07.793

    Example:

    1. select DATEADD(d,1,getdate())

    Result:

    2009-10-27 14:05:29.950

    Example:

    1. select DATEADD(m,3,getdate())

    Result:

    2010-01-26 14:05:39.327

    DATEDIFF:


    The DATEDIFF function returns the number of date-parts between a start date and an end date.

    Syntax:

    1. DATEDIFF (datepart, startdate, enddate)

    Example:

    1. select DATEDIFF(d,'2009-10-26 11:01:59.420','2009-11-8 11:01:59.420')

    Result:

    13

    Example:

    1. select DATEDIFF(m,'2009-10-26 11:01:59.420','2010-5-30 11:01:59.420')

    Result:

    7

    DATENAME:


    The DATENAME function returns the name of a specified date-part for a given date/time.

    Syntax:

    1. DATENAME ( datepart , date )

    Example:

    1. select getdate()

    Result:

    2009-10-26 14:04:07.793

    Example:

    1. select DATENAME (m,getdate())

    Result:

    October

    Example:

    1. select DATENAME (wk,getdate())

    Result:

    44

    All the above functions accept the following date-parts,

    Date PartAbbreviation
    Yearyy or yyyy
    Quarterqq or q
    Monthmm or m
    Weekwk or ww
    Weekdaydw
    dayofyeardy or y
    Daydd or d
    Hourhh
    Minutemi, n
    Secondss, s
    Millisecondms

    Comments

     

    Post a comment

    Please correct the following: