In SQL Server, the following date and time function are used:

Dateadd()

It is used to add an interval to a date.

Syntax:

DATEADD (datepart, number, expression)

Example:

SELECT DATEADD(day, 09,'2011-02-06 01:03:00.000')

Output:

2011-02-15 01:03:00.000

Datediff()

The DATEDIFF function is used to calculate the difference between two days.

Syntax:

DATEDIFF (datepart, expression1, expression2)

Example:

SELECT DATEDIFF(day, '2011-01-11','2011-01-06')

Output:

-5

Datename()

It Returns a character string representing the specified datepart of the specified date.

Syntax:

DATENAME (datepart,date)

Example:

SELECT DATENAME(month, GETDATE())

Output:

January

Datepart()

It provides a specific part of the date/time value.

Syntax:

DATEPART (part_of_day, expression)

Example:

SELECT DATEPART (yyyy,'2011-02-15')

Output:

2011

Getdate()

It is used to retrieve the current database system time in SQL Server.

Syntax:

GETDATE()

Note: it does not contain any parameter.

Example:

Select GETDATE()

Output:

2011-01-31 20:47:17.270

Getutcdate()

It returns the datetime value that represents the current UTC time

Syntax:

Getutcdate()

Example:

Getutcdate()

Output:

2011-01-31 15:18:01.180

Year()

It returns an integer that represents the year of the specified date.

Syntax:

YEAR (date)

Example:

Select Year(getdate())

Output:

2011

Month()

It returns an integer that represents the month of the specified date.

Syntax:

Month(date)

Example:

Select month(getdate())

Output:

1

Day()

It returns an integer that represents the day of the specified date.

Syntax:

Day(date)

Example:

Select Day(getdate())

Output:

31