SQL SERVER - Date And Time Functions

Following are the types of Date and Time functions that are available in SQL Server.

DateAdd()
DateDiff()
DatePart()
DateName()                                                                   GetDate()                                                                             Year()                                                                                         Month()                                                                                  Day()

Let us see Where and How these Functions can be used.

1. GetDate()  : Returns the Current System Date and Time

     Syntax : GetDate()

     Example : Select GetDate() [Current Date]

     Result :

      

2. DateAdd() : Returns the Date after Adding the specific interval (it may be either days or months or years) to the given Date.

     Syntax : DateAdd(Datepart,Integer,Date)

     Example : Select DateAdd(dd,27,GetDate()) [New Date]

     Result :

           

3. DateDiff() : Returns the  Difference  between  two Specified Dates.

     Syntax : DateDiff(Datepart,Integer,Date)

     Example : Select DateDiff(dd,'01/01/2009',GetDate()) [Date Difference]

     Result :

    

4. DatePart() : Returns an integer which represents the  specified Date Part of the Specified Date.

     Syntax : DatePart(Datepart,Date)

     Example : Select DatePart(dw,'01/26/2009') [Week Day]

     Result :

   

5. DateName() : Returns the Character string which represents the specified Date Part of the Specified Date.

     Syntax : DateName(Datepart,Date)

     Example : Select DateName(month,'05/27/2009') [Month]

     Result :

    

6. Year() :  Returns the Year of the Specified date

     Syntax :  Year(Date)

     Example : Select Year('05/27/2009') [Year]

     Result :

   

7. Month() : Returns the Month of the Specified date

     Syntax :  Month(Date)

     Example : Select Month('05/27/2009') [Month]

     Result :

   

8. Day() :  Returns the Day of the Specified date

     Syntax :  Day(Date)

     Example : Select Day('05/27/2009') [Day]

     Result :