Date functions

You'll be presented to the SQL functions that can perform date manipulations.


CURRENT_DATE

Returns the current date value.

Syntax: CURRENT_DATE()
SELECT CURRENT_DATE();
-- Result: 2018-02-01

CURRENT_TIMESTAMP

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.

Syntax: CURRENT_TIMESTAMP()
SELECT CURRENT_TIMESTAMP();
-- Result: 2018-02-01 03:04:05

DATEADD

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

Requires:

  • datepart: The part of the date to add the specified number to. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
  • number: The number to be added.
  • date: The expression of the datetime data type.
  • dateformat (optional): The output date format.
Syntax: DATEADD (datepart, integer_number, date [, dateformat])
SELECT DATEADD('d', 5, '2018-02-01');
-- Result: 2018-02-06

SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
-- Result: 2018-02-01 05:00:00

DATEDIFF

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

Requires:

  • datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
  • startdate: The datetime expression of the start date.
  • enddate: The datetime expression of the end date.
Syntax: DATEDIFF(datepart, startdate, enddate)
SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
-- Result: 9

SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
-- Result: 12

DATEFROMPARTS

Returns the datetime value for the specified year, month, and day.

Requires:

  • integer_year: The integer expression specifying the year.
  • integer_month: The integer expression specifying the month.
  • integer_day: The integer expression specifying the day.
Syntax: DATEFROMPARTS(integer_year, integer_month, integer_day)
SELECT DATEFROMPARTS(2018, 2, 1);
-- Result: 2018-02-01

DATENAME

Returns the character string that represents the specified date part of the specified date.

Requires:

  • datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
  • date: The datetime expression.
Syntax: DATENAME(datepart, date)
SELECT DATENAME('yy', '2018-02-01');
-- Result: '2018'

SELECT DATENAME('dw', '2018-02-01');
-- Result: 'Thursday'

DATEPART

Returns a character string that represents the specified date part of the specified date.

Requires:

  • datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
  • date: The datetime string.
  • integer_datefirst (optional): The optional integer representing the first day of the week. The default is 7, Sunday.
Syntax: DATEPART(datepart, date [, integer_datefirst])
SELECT DATEPART('yy', '2018-02-01');
-- Result: 2018

SELECT DATEPART('dw', '2018-02-01');
-- Result: 5

DATETIME2FROMPARTS

Returns the datetime value for the specified date parts, with precision.

Requires:

  • year: The integer expression specifying the year.
  • month: The integer expression specifying the month.
  • day: The integer expression specifying the day.
  • hour: The integer expression specifying the hour.
  • minute: The integer expression specifying the minute.
  • seconds: The integer expression specifying the seconds.
  • fractions: The integer expression specifying the fractions of the second.
  • precision: The integer expression specifying the precision of the fraction.
Syntax: DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)
SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
-- Result: 2018-02-01 01:02:03.456

DATETIMEFROMPARTS

Returns the datetime value for the specified date parts.

Requires:

  • year: The integer expression specifying the year.
  • month: The integer expression specifying the month.
  • day: The integer expression specifying the day.
  • hour: The integer expression specifying the hour.
  • minute: The integer expression specifying the minute.
  • seconds: The integer expression specifying the seconds.
  • milliseconds: The integer expression specifying the milliseconds.
Syntax: DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, mililiseconds)
SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
-- Result: 2018-02-01 01:02:03.456

DAY

Returns the integer that specifies the day component of the specified date.

Requires:

  • date: The datetime string that specifies the date.
Syntax: DAY(date)
SELECT DAY('2018-02-01');
-- Result: 1

EOMONTH

Returns the last day of the month that contains the specified date with an optional offset.

Requires:

  • start_date: the datetime expression specifying the date for which to return the last day of the month.
  • integer_month_to_add (optional): The integer expression specifying the number of months to add to start_date.
Syntax: EOMONTH(start_date [, integer_month_to_add ])
SELECT EOMONTH('2018-02-01');
-- Result: 2018-02-28

SELECT EOMONTH('2018-02-01', 2);
-- Result: 2018-04-30

GETDATE

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.

Syntax: GETDATE()
SELECT GETDATE();
-- Result: 2018-02-01 03:04:05

GETUTCDATE

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME. .

Syntax: GETUTCDATE()
SELECT GETUTCDATE();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05

ISDATE

Returns 1 if the value is a valid date, time, or datetime value; otherwise returns 0.

Requires:

  • date: The datetime string.
  • date_format (optional): The datetime format.
Syntax: ISDATE(date, [date_format])
SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
-- Result: 1

SELECT ISDATE('Not a date');
-- Result: 0

SMALLDATETIMEFROMPARTS

Returns the datetime value for the specified date and time.

Requires:

  • year: The integer expression specifying the year.
  • month: The integer expression specifying the month.
  • day: The integer expression specifying the day.
  • hour: The integer expression specifying the hour.
  • minute: The integer expression specifying the minute.
Syntax: SMALLDATETIMEFROMPARTS(year, month, day, hour, minute)
SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
-- Result: 2018-02-01 01:02:004

SYSDATETIME

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.

Syntax: SYSDATETIME()
SELECT SYSDATETIME();
-- Result: 2018-02-01 03:04:05

SYSUTCDATETIME

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

Syntax: SYSUTCDATETIME()
SELECT SYSUTCDATETIME();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05

TIMEFROMPARTS

Returns the time value for the specified time and with the specified precision.

Requires:

  • hour: The integer expression specifying the hour.
  • minute: The integer expression specifying the minute.
  • seconds: The integer expression specifying the seconds.
  • fractions: The integer expression specifying the fractions of the second.
  • precision: The integer expression specifying the precision of the fraction.
Syntax: TIMEFROMPARTS(hour, minute, seconds, fractions, precision)
SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
-- Result: 01:02:03.456

YEAR

Returns the integer that specifies the year of the specified date.

Requires:

  • date: The datetime expression.
Syntax: YEAR(date)
SELECT YEAR('2018-02-01');
-- Result: 2018