MSDN Documentation

Date and Time Functions

This section details the various functions available in SQL for manipulating and retrieving date and time values. These functions are crucial for managing temporal data, scheduling, and reporting.

GETDATE()

Returns the current database system date and time as a datetime value.

GETDATE()
Syntax:
GETDATE()

Returns: datetime

Description: This is a fundamental function for capturing the timestamp of an operation or for comparing against specific dates.

DATEADD(datepart, number, date)

Returns a new datetime value resulting from adding a specified time interval to a specified date.

DATEADD(day, 7, '2023-10-26')
Syntax:
DATEADD (datepart , number , date )

Parameters:

  • datepart: The part of the date to which the number is added (e.g., year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond).
  • number: The integer value to add to the datepart. Can be positive or negative.
  • date: The date to which the interval is added.

Returns: datetime

DATEDIFF(datepart, startdate, enddate)

Returns the number of date part boundaries crossed between two specified dates.

DATEDIFF(day, '2023-10-20', '2023-10-26')
Syntax:
DATEDIFF ( datepart , startdate , enddate )

Parameters:

  • datepart: The part of the date in which to return the difference (e.g., year, month, day).
  • startdate: The date that is the earlier date.
  • enddate: The date that is the later date.

Returns: integer

DATENAME(datepart, date)

Returns a character string representing the specified date part of the specified date.

DATENAME(weekday, '2023-10-26')
Syntax:
DATENAME ( datepart , date )

Parameters:

  • datepart: The part of the date for which to return the name (e.g., year, month, day, weekday).
  • date: The date expression.

Returns: varchar

DATEPART(datepart, date)

Returns an integer representing the specified date part of the specified date.

DATEPART(month, '2023-10-26')
Syntax:
DATEPART ( datepart , date )

Parameters:

  • datepart: The part of the date to return (e.g., year, month, day, hour).
  • date: The date expression.

Returns: integer

DAY(date)

Returns the day of the month as an integer.

DAY('2023-10-26')
Syntax:
DAY ( date )

Parameters:

  • date: A valid date expression.

Returns: integer

MONTH(date)

Returns the month as an integer.

MONTH('2023-10-26')
Syntax:
MONTH ( date )

Parameters:

  • date: A valid date expression.

Returns: integer

YEAR(date)

Returns the year as an integer.

YEAR('2023-10-26')
Syntax:
YEAR ( date )

Parameters:

  • date: A valid date expression.

Returns: integer

FORMAT(value, format [, culture])

Formats a value, like a date or a number, and converts it to a string. This is available from SQL Server 2012.

FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')
Syntax:
FORMAT ( value , format [, culture ] )

Parameters:

  • value: The value to format.
  • format: The format string.
  • culture: Optional. The culture to use for formatting.

Returns: nvarchar