Date and Time Functions in SQL Server

There is a lot of date and Time related system defined functions in sql server as below.

  1. CURRENT_TIMESTAMP : return date and time of system where sql instance is installed. 
  2. GETDATE ( ) : return date and time of system where sql instance is installed. 
  3. GETUTCDATE ( )  : (Coordinated Universal Time).

Function that use with date and time part, number.

DATENAMEdatepart , date ) :  Returns a character string that represents the specified datepart of the specified date.

Example:  SELECT DATENAME(MM,GETDATE()) — MM stands for month.

DATEPARTdatepart , date ): Returns an integer that represents the specified datepart of the specified date.

Example:  SELECT DATEPART(MM,GETDATE())  — MM stands for month.

DATEADD (datepartnumber , date ):Add the given interval into date. 

Example:  SELECT DATEADD (MM,1,GETDATE())  — add one month to current date.

DATEDIFF ( datepart , startdate , enddate ): Difference between two date in given datepart format.

Example:  SELECT DATEADD (DD,GETDATE(), GETDATE()+1)  — return 1 as there is one day difference between current date and current+oneday.

DAY (date) : give the day number in integer for specified date.  

Example: SELECT DAY(GETDATE()) — Return the day number of current date

Month(date) : give the month number in integer for specified date.  

Example: SELECT Month(GETDATE()) — Return the month number of current date

YEAR(date) : give the Year number in integer for specified date in four digit.  

Example: SELECT YEAR(GETDATE()) — Return the Year number of current date like 2017