Commonly used Date Functions

          Several functions are available in SQL server, in those commonly used functions are

Date time functions:

• GETDATE()
• DATEDIFF()
• DATEPART()
• DATENAME()
• YEAR()
• MONTH()
• DAY()

GETDATE(): The GETDATE() function extracts current date time from the server.

Example: Select GETDATE()

DATEDIFF(): We can find the date difference between two date time elements.

            Syntax: DATEDIFF(datepart, startdate, enddate)

            Example:

            select DATEDIFF (YY,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (DD,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (HH,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

DATEPART(): We can select a part of date or time.

            Syntax: DATEPART(datepart, date)

            Example:

            SELECT DATEPART(YYYY,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(DD,'2012-01-01 02:05:00');

SELECT DATEPART(HH,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(SECOND,'2012-01-01 02:05:05');

DATENAME(): We can find the date name from date element.

            Example:

SELECT DATENAME(MM,'2012-01-01 02:05:00');

SELECT DATENAME(DW,'2012-01-01 02:05:00');

YEAR(): We can extract year part from the date time element.

            Example: SELECT YEAR('2012-01-01 02:05:00');

MONTH(): We can extract month part from the date time element.

            Example: SELECT MONTH('2012-01-01 02:05:00');

DAY(): We can extract day part from the date time element.

            Example: SELECT DAY('2012-01-01 02:05:00');