Search

DATE

DATE

DATE functions can be used to construct, extract, or modify date, time, and timestamp data.

Date literal functions begin with L_ and are used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for date literal functions, <= and >= are not.

CURRENT_DATE

Returns the current date value.

Syntax

CURRENT_DATE()

Example(s)

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()

Example(s)

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.

Syntax

DATEADD (datepart , integer_number , date [, dateformat])

Parameters

  • 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 The optional output date format.

Example(s)

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.

Syntax

DATEDIFF ( datepart , startdate , enddate )

Parameters

  • 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 day (dd, d), 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.

Example(s)

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.

Syntax

DATEFROMPARTS(integer_year, integer_month, integer_day)

Parameters

  • year The integer expression specifying the year.
  • month The integer expression specifying the month.
  • day The integer expression specifying the day.

Example(s)

SELECT DATEFROMPARTS(2018, 2, 1);
-- Result: 2018-02-01

DATENAME

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

Syntax

DATENAME(datepart , date)

Parameters

  • 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), millisecond (ms), microsecond (mcs), nanosecond (ns), and TZoffset (tz).
  • date The datetime expression.

Example(s)

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.

Syntax

DATEPART(datepart, date [,integer_datefirst])

Parameters

  • 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), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
  • date The datetime string that specifies the date.
  • datefirst The optional integer representing the first day of the week. The default is 7, Sunday.

Example(s)

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

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

DATETIMEFROMPARTS

Returns the datetime value for the specified date parts.

Syntax

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Parameters

  • 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.

Example(s)

SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
-- Result: 2018-02-01 01:02:03.456

DATETIME2FROMPARTS

Returns the datetime value for the specified date parts (with different parameters).

Syntax

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Parameters

  • 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.

Example(s)

SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
-- Result: 2018-02-01 01:02:03.456

DATE_TRUNC

Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.

Syntax

DATE_TRUNC(date, datepart)

Parameters

  • date The datetime string that specifies the date.
  • datepart Refer to the Oracle documentation for valid datepart syntax.

Example(s)

SELECT DATE_TRUNC('05-04-2005', 'YY');
-- Result: '1/1/2005'

SELECT DATE_TRUNC('05-04-2005', 'MM');
-- Result: '5/1/2005'

DATE_TRUNC2

Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.

Syntax

DATE_TRUNC2(datepart, date, [weekday])

Parameters

  • datepart One of ‘millennium’, ‘century’, ‘decade’, ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’ or ‘second’.
  • date The datetime string that specifies the date.
  • weekday The optional day of the week to use as the first day for ‘week’. One of ‘sunday’, ‘monday’, etc.

Example(s)

SELECT DATE_TRUNC2('year', '2020-02-04');
-- Result: '2020-01-01'

SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
-- Result: '2020-02-02', which is` `the previous Monday

DAY

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

Syntax

DAY(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT DAY('2018-02-01');
-- Result: 1

DAYOFMONTH

Returns the day of the month of the given date part.

Syntax

DAYOFMONTH(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT DAYOFMONTH('04/15/2000');
-- Result: 15

DAYOFWEEK

Returns the day of the week of the given date part.

Syntax

DAYOFWEEK(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT DAYOFWEEK('04/15/2000');
-- Result: 7

DAYOFYEAR

Returns the day of the year of the given date part.

Syntax

DAYOFYEAR(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT DAYOFYEAR('04/15/2000');
-- Result: 106

EOMONTH

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

Syntax

EOMONTH(date [, integer_month_to_add ]) or LAST_DAY(date)

Parameters

  • date The datetime expression specifying the date for which to return the last day of the month.
  • integer_month_to_add The optional integer expression specifying the number of months to add to the date before calculating the end of the month.

Example(s)

SELECT EOMONTH('2018-02-01');
-- Result: 2018-02-28

SELECT LAST_DAY('2018-02-01');
-- Result: 2018-02-28

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

FDMONTH

Returns the first day of the month of the given date part.

Syntax

FDMONTH(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT FDMONTH('02-08-2018');
-- Result: 2/1/2018

FDQUARTER

Returns the first day of the quarter of the given date part.

Syntax

FDQUARTER(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT FDQUARTER('05-08-2018');
-- Result: 4/1/2018

FDWEEK

Returns the first day of the week of the given date part.

Syntax

FDWEEK(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT FDWEEK('02-08-2018');
-- Result: 2/4/2018

FILEMODIFIEDTIME

Returns the time stamp associated with the Date Modified of the relevant file.

Syntax

FILEMODIFIEDTIME(uri)

Parameters

  • uri An absolute path pointing to a file on the local file system.

Example(s)

SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt');
-- Result: 6/25/2019 10:06:58 AM

FROM_DAYS

Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.

Syntax

FROM_DAYS(datevalue)

Parameters

  • datevalue A integer value representing the number of days since 1582-10-15.

Example(s)

SELECT FROM_DAYS(736000);
-- Result: 2/6/2015

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()

Example(s)

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()

Example(s)

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

HOUR

Returns the hour component from the provided datetime.

Syntax

HOUR(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT HOUR('02-02-2020 11:30:00');
-- Result: 11

ISDATE

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

Syntax

ISDATE(date, [date_format])

Parameters

  • date The datetime string that specifies the date.
  • date_format The optional datetime format.

Example(s)

SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
-- Result: 1

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

L_LAST_N_DAYS

The previous n days, excluding the current day.

Syntax

L_LAST_N_DAYS(n)

Example(s)

SELECT * FROM MyTable WHERE MyDateField = LAST_N_DAYS(3)

L_LAST_N_WEEKS

Every day in every week, starting n weeks before current week, and ending in the previous week.

Syntax

L_LAST_N_WEEKS(n)

Example(s)

SELECT * FROM MyTable WHERE MyDateField = LAST_N_WEEKS(3)

L_LAST_WEEK

Every day in the preceding week.

Syntax

L_LAST_WEEK()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_NEXT_N_DAYS

The following n days, including the current day.

Syntax

L_NEXT_N_DAYS(n)

Example(s)

SELECT * FROM MyTable WHERE MyDateField = NEXT_N_DAYS(3)

Also available:

  • LAST/NEXT_90_DAYS

L_NEXT_N_WEEKS

Every day in every week, starting the following week, and ending n weeks in the future.

Syntax

L_NEXT_N_WEEKS(n)

Example(s)

SELECT * FROM MyTable WHERE MyDateField = NEXT_N_WEEKS(3)

Also available:

  • LAST/NEXT_N_MONTHS(n)
  • LAST/NEXT_N_QUARTERS(n)
  • LAST/NEXT_N_YEARS(n)

L_NEXT_WEEK

Every day in the following week.

Syntax

L_NEXT_WEEK()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = NEXT_WEEK()

Also available:

  • LAST/THIS/NEXT MONTH
  • LAST/THIS/NEXT QUARTER
  • LAST/THIS/NEXT YEAR

L_THIS_WEEK

Every day in the current week.

Syntax

L_THIS_WEEK()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = THIS_WEEK()

L_TODAY

The current day.

Syntax

L_TODAY()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = TODAY()

L_TOMORROW

The following day.

Syntax

L_TOMORROW()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = TOMORROW()

L_YESTERDAY

The previous day.

Syntax

L_YESTERDAY()

Example(s)

SELECT * FROM MyTable WHERE MyDateField = YESTERDAY()

LAST_MONTH

Returns a time stamp equivalent to exactly one month before the current date.

Syntax

LAST_MONTH()

Example(s)

SELECT LAST_MONTH(); //Assume the date is 3/17/2020
-- Result: 2/17/2020

LAST_WEEK

Returns a time stamp equivalent to exactly one week before the current date.

Syntax

LAST_WEEK()

Example(s)

SELECT LAST_WEEK(); //Assume the date is 3/17/2020
-- Result: 3/10/2020

LAST_YEAR

Returns a time stamp equivalent to exactly one year before the current date.

Syntax

LAST_YEAR()

Example(s)

SELECT LAST_YEAR(); //Assume the date is 3/17/2020
-- Result: 3/10/2019

LDMONTH

Returns the last day of the provided month.

Syntax

LDMONTH(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT LDMONTH('02-02-2020');
-- Result: 2/29/2020

LDQUARTER

Returns the last day of the provided quarter.

Syntax

LDQUARTER(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT LDQUARTER('02-02-2020');
-- Result: 3/31/2020

LDWEEK

Returns the last day of the provided week.

Syntax

LDWEEK(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT LDWEEK('02-02-2020');
-- Result: 2/8/2020

MAKEDATE

Returns a date value from a year and a number of days.

Syntax

MAKEDATE(year, days)

Parameters

  • year The year
  • days The number of days into the year. Value must be greater than 0.

Example(s)

SELECT MAKEDATE(2020, 1);
-- Result: 2020-01-01

MINUTE

Returns the minute component from the provided datetime.

Syntax

MINUTE(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT MINUTE('02-02-2020 11:15:00');
-- Result: 15

MONTH

Returns the month component from the provided datetime.

Syntax

MONTH(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT MONTH('02-02-2020');
-- Result: 2

QUARTER

Returns the quarter associated with the provided datetime.

Syntax

QUARTER(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT QUARTER('02-02-2020');
-- Result: 1

SECOND

Returns the second component from the provided datetime.

Syntax

SECOND(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT SECOND('02-02-2020 11:15:23');
-- Result: 23

SMALLDATETIMEFROMPARTS

Returns the datetime value for the specified date and time.

Syntax

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Parameters

  • 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.

Example(s)

SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
-- Result: 2018-02-01 01:02:00

STRTODATE

Parses the provided string value and returns the corresponding datetime.

Syntax

STRTODATE(string,format)

Parameters

  • string The string value to be converted to datetime format.
  • format A format string which describes how to interpret the first string input. Follows standard .NET date format syntax. A few special formats are available as well, including UNIX, UNIXMILIS, TICKS, and FILETICKS.

Example(s)

SELECT STRTODATE('03*04*2020','dd*MM*yyyy');
-- Result: 4/3/2020

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()

Example(s)

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()

Example(s)

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.

Syntax

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Parameters

  • 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.

Example(s)

SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
-- Result: 01:02:03.456

TO_DAYS

Returns the number of days since 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.

Syntax

TO_DAYS(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT TO_DAYS('02-06-2015');
-- Result: 736000

WEEK

Returns the week (of the year) associated with the provided datetime.

Syntax

WEEK(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT WEEK('02-17-2020 11:15:23');
-- Result: 8

YEAR

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

Syntax

YEAR(date)

Parameters

  • date The datetime string that specifies the date.

Example(s)

SELECT YEAR('2018-02-01');
-- Result: 2018