Date and Time Functions

basic date and time functions add months adds the specified number of months to the date equivalent to using a date and adding a month(value) syntax add months(time, int) argument data type description time timestamp or date a time value , specified as a timestamp or date, which the function modifies by adding a specified number of months int int the number of months to add to the time value example select add months('2022 11 15 04 18 00',2); output 2023 01 15 century returns the number of centuries syntax century(time) argument data type description time timestamp or date a time value , specified as a timestamp or date, which the function computes to return the number of its centuries example in this example, the current date is 2023 01 03 select century(current date()); output 21 curdate alias for current date current date alias for curdate returns the current date in the format yyyy mm dd syntax current date example select current date; output 2023 01 03 current time returns the current time as a time value (e g , hh\ mm\ ss mm ) syntax current time example select current time; output 19 40 04 current timestamp alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu returns the current date and time as a timestamp value (e g , yyyy mm dd hh\ mm\ ss mmm ) syntax current timestamp example select current timestamp; output 2023 01 03 11 00 03 316674397 date part alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu date trunc returns the date or timestamp entered, truncated to the specified precision syntax date trunc(precision, time) argument data type description precision char the timestamp unit used to truncate the returned value for the precision input, acceptable values are nanosecond microsecond millisecond second minute hour day week month quarter year decade century millennium time timestamp or date a time value, specified as a timestamp or date, which the function truncates to the specified precision the time input supports precision values day or larger example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select date trunc('nanosecond', current timestamp()); output 2023 01 03 11 01 05 280975906 example this example uses current timestamp set to 2023 01 01 11 00 59 114058181 select date trunc('month', current timestamp()); output 2023 01 01 00 00 00 000000000 example this example uses current timestamp set to 2023 01 01 11 00 59 114058181 select date trunc('century', current timestamp()); output 2001 01 01 00 00 00 000000000 day alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu day of week returns an integer, in the range of 1 to 7, that represents the day of the week the value 1 is sunday, and 7 is saturday syntax day of week(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its day of the week example select day of week(current date()); output 3 day of year alias for doy returns an integer in the range 1 to 366 that represents the day of the year syntax day of year(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its day of year example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select day of year(current timestamp()); output 3 day of month alias for day extracts the day of month portion of a timestamp or date as an integer syntax day of month(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its day of the month example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select day(current timestamp()); output 3 decade the decade is the year divided by 10 syntax decade(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its decade example this example uses current date set to 2023 01 03 select decade(current date()); output 202 dow alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu doy alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu eomonth returns the last day of the month using the specified timestamp or date if you specify both arguments, this function returns the last day of the month for the resulting timestamp or date after the system adds the specified number of months to the first argument value syntax eomonth(time \[, int]) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for the last day of the month int int optional an integer value that represents the number of months to add to the time value if unspecified, the value defaults to 0 examples this example returns the last day of the month for the current timestamp select eomonth(current timestamp(),0); output 2023 02 27 this example returns the last day of the month in november in 2022 by using the eomonth function to add one month to the last day of october in 2022 select eomonth(date('2022 10 31'), 1); output 2022 11 30 epoch the number of seconds after 1970 01 01 00 00 00 utc syntax epoch(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for the number of seconds after 1970 01 01 00 00 00 utc example select epoch(current timestamp()); output 1672743901 extract extract a component from a timestamp or date syntax extract(precision from time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for the component to be extracted precision char the timestamp unit to be extracted for the precision input, acceptable values are nanosecond microsecond millisecond second minute hour day week month quarter year decade century millennium example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select extract(hour from current timestamp()); output 11 0 hour extracts the hour portion of a timestamp as an integer syntax hour(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its hour value example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select hour(current timestamp()); output 11 isodow extracts the day of the week based on iso 8601, which ranges from monday (1) to sunday (7) syntax isodow(timestamp or date) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its day of week value example this example uses current timestamp set to 2023 01 03 11 00 59 114058182 select isodow(current timestamp()); output 2 isdate returns true if the input argument can be successfully cast to a date, false otherwise syntax isdate(char) argument data type description char char a string to cast as a date example select isdate('2023 01 03'); output true makedatetime returns a timestamp consisting of the specified date and time syntax makedatetime(date, time) argument data type description date char a date string for timestamp creation time char a time string for timestamp creation example select makedatetime('2022 11 02','01 24 58'); output 2022 11 02 01 24 58 000000000 millisecond extracts the millisecond portion of a timestamp as an integer syntax millisecond(timestring) argument data type description timestring timestamp a time value, specified as a timestamp or date, which the function extracts for the millisecond value example this example uses current timestamp set to 2023 02 22 13 59 25 286 select millisecond(current timestamp()); output 286 minute extracts the minute portion of a timestamp or date as an integer syntax minute(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function extracts for the minute value example this example uses current timestamp set to 2023 02 22 14 00 40 764 select minute(current timestamp()); output 0 month name returns the calendar name in english of the month for the specified date syntax month name(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes its month name example this example uses current timestamp set to 2023 02 22 13 59 25 286 select month name(current timestamp()); output february months between returns the difference between the two dates or timestamps in months as a double the fractional months component is based on a 31 day month if the two dates have the same day or are both the last day of the month, this function returns a whole number instead syntax months between(time1, time2) argument data type description time1 timestamp or date a time value, specified as a timestamp or date, which the function compares to time2 for the difference in months time2 timestamp or date a time value, specified as a timestamp or date, which the function compares to time1 for the difference in months example select months between(current timestamp(), add months(current timestamp(),11)); output 11 month extracts the month portion of a timestamp or date as an integer syntax month(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes for its month value example this example uses current timestamp set to 2023 02 22 13 59 25 286 select month(current timestamp()); output 2 msecs the seconds field, including fractional parts this is the same as extract(milliseconds from time) syntax msecs(time) argument data type description time timestamp or date a time value, specified as a timestamp or date the function multiplies the seconds part of the value by 1,000 example select msecs(current timestamp()); output 37637 648 nanos to timestamp convert a number of nanoseconds into a timestamp equivalent to the duration after the epoch time, 1970 01 01 00 00 00 utc syntax nanos to timestamp(time) argument data type description ns int a number of nanoseconds, specified as an integer, which the function converts into a timestamp equivalent to the duration after the epoch time example select nanos to timestamp(10000000); output 1969 12 31 16 00 00 010 next day returns the closest date after a specified date that lies on a specific day of the week syntax next day(date, character) argument data type description date date , timestamp , or char a time value, specified as a date, timestamp, or string, which the function uses to return the closest date for a specific day of the week can be any type that can be cast to date character char a string representing a day of the week the string must match the first three characters (case insensitive) of the english name of any day of the week if this string does not match the prefix of any day, this function returns null example select next day('2023 02 27', 'sat'); output 2023 03 04 now alias for date and time functions docid\ ollvrg4vjupmvwhgpupdu quarter returns an integer between 1 and 4 representing the quarter of the year in which the specified date falls syntax quarter(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function computes to return the quarter of the year example select quarter(date('2023 02 08 09 00 00 000')); output 1 round returns the specified date or timestamp, rounded to the specified precision the precision argument behaves like date and time functions docid\ ollvrg4vjupmvwhgpupdu the function rounds up values to the specified precision syntax round(time, precision) argument data type description precision char the timestamp unit used to round the returned value for the precision input, acceptable values are nanosecond microsecond millisecond second minute hour day week month quarter year decade century millennium time timestamp or date a time value, specified as a timestamp or date, which the function rounds to the specified precision example select round(current timestamp() ,'hour'); output 2023 02 08 09 00 00 000 second extracts the seconds portion of a timestamp as an integer syntax second(time) argument data type description time timestamp or time a time value, specified as a timestamp or date, which the function uses to return the seconds value example this example uses current timestamp set to 2023 02 28 13 17 30 243 select second(current timestamp()); output 30 timestamp to nanos convert timestamp into nanoseconds after epoch as bigint syntax timestamp to nanos(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function uses to return the nanoseconds after the epoch example this example uses current timestamp set to 2023 02 28 13 20 12 373 select timestamp to nanos(current timestamp()); output 1677619212373708904 to timestamp for information on using the to timestamp conversion function, see the formatting functions docid\ g ia4znpotfx2kegcvv2r page usecs returns the seconds part of a time value, including fractional parts, as an integer same as extract(microseconds from time) syntax usecs(time) argument data type description time timestamp or date a time value, specified as a timestamp or date the function multiplies the seconds part of the value by 1,000,000 example this example uses current timestamp set to 2023 02 28 13 32 17 438 select usecs(current timestamp()); output 17438041 week returns the iso 8601 week number, as an integer, of the specified timestamp or date value the week starts on monday and the first week of a year contains january 4 of that year for details, see the iso week date definition syntax week(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function uses to return the week number example this example uses current timestamp set to 2023 02 28 13 37 37 128 select week(current timestamp()); output 9 year extracts the year portion of a timestamp or date as an integer syntax year(time) argument data type description time timestamp or date a time value, specified as a timestamp or date, which the function uses to extract the year example this example uses current timestamp set to 2023 02 28 13 37 37 128 select year(current timestamp()); output 2023 advanced date and time functions dateadd this function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value the data type of the returned value for this function is dynamic the return type depends on the argument supplied for date if the value for date is a string literal date , dateadd returns a datetime value if another valid input data type is supplied for date , dateadd returns the same data type dateadd raises an error if the string literal seconds scale exceeds three decimal place positions ( nnn) or if the string literal contains the time zone offset part syntax dateadd (datepart, number, date) argument data type description datepart char the part of date to which dateadd adds an integer number see the datepart values table for a list of supported arguments number int an expression that can resolve to an int that dateadd adds to the specified datepart value of date dateadd accepts user defined variable values for number dateadd truncates a specified number value that has a decimal fraction in this situation, it does not round the number value date time , timestamp , date a column expression, expression, string literal, or user defined variable a string literal value must resolve to a date or timestamp use four digit years to avoid ambiguity issues datepart abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns dateadd does not accept user defined variable equivalents for the datepart arguments examples this example shows each result from incrementing different datepart values by 1 select 'year', dateadd(year,1, '2007 01 01 13 10 10 1111111') union all select 'quarter', dateadd(quarter,1, '2007 01 01 13 10 10 1111111') union all select 'month', dateadd(month,1, '2007 01 01 13 10 10 1111111') union all select 'dayofyear', dateadd(dayofyear,1, '2007 01 01 13 10 10 1111111') union all select 'day', dateadd(day,1, '2007 01 01 13 10 10 1111111') union all select 'week', dateadd(week,1, '2007 01 01 13 10 10 1111111') union all select 'weekday', dateadd(weekday,1, '2007 01 01 13 10 10 1111111') union all select 'hour', dateadd(hour,1, '2007 01 01 13 10 10 1111111') union all select 'minute', dateadd(minute,1, '2007 01 01 13 10 10 1111111') union all select 'second', dateadd(second,1, '2007 01 01 13 10 10 1111111') union all select 'millisecond', dateadd(millisecond,1, '2007 01 01 13 10 10 1111111') union all select 'microsecond', dateadd(microsecond,1, '2007 01 01 13 10 10 1111111') union all select 'nanosecond', dateadd(nanosecond,1, '2007 01 01 13 10 10 1111111'); output week 2007 01 08 13 10 10 111111100 millisecond 2007 01 01 13 10 10 112111100 second 2007 01 01 13 10 11 111111100 day 2007 01 02 13 10 10 111111100 minute 2007 01 01 13 11 10 111111100 dayofyear 2007 01 02 13 10 10 111111100 quarter 2007 04 01 13 10 10 111111100 month 2007 02 01 13 10 10 111111100 hour 2007 01 01 14 10 10 111111100 weekday 2007 01 02 13 10 10 111111100 year 2008 01 01 13 10 10 111111100 nanosecond 2007 01 01 13 10 10 111111101 microsecond 2007 01 01 13 10 10 111112100 fetched 13 rows in this example, each statement increments datepart by a number large enough to increment the next higher unit of date or time select 'quarter', dateadd(quarter,4,'2007 01 01 01 01 01 1111111') 	union all select 'month', dateadd(month,13,'2007 01 01 01 01 01 1111111') 	union all select 'dayofyear', dateadd(dayofyear,365,'2007 01 01 01 01 01 1111111') 	union all select 'day', dateadd(day,365,'2007 01 01 01 01 01 1111111') 	union all select 'week', dateadd(week,5,'2007 01 01 01 01 01 1111111') 	union all select 'weekday', dateadd(weekday,31,'2007 01 01 01 01 01 1111111') 	union all select 'hour', dateadd(hour,23,'2007 01 01 01 01 01 1111111') 	union all select 'minute', dateadd(minute,59,'2007 01 01 01 01 01 1111111') 	union all select 'second', dateadd(second,59,'2007 01 01 01 01 01 1111111'); output day 2008 01 01 01 01 01 111111100 weekday 2007 02 01 01 01 01 111111100 second 2007 01 01 01 02 00 111111100 month 2008 02 01 01 01 01 111111100 quarter 2008 01 01 01 01 01 111111100 dayofyear 2008 01 01 01 01 01 111111100 week 2007 02 05 01 01 01 111111100 hour 2007 01 02 00 01 01 111111100 minute 2007 01 01 02 00 01 111111100 fetched 9 rows datediff this function returns an int representing the difference between two date or time values, in a specified date or time unit if only a time value is assigned to a date data type variable, datediff sets the value of the missing datepart to the default value 1900 01 01 if only a date value is assigned to a variable of a time or date data type, datediff sets the value of the missing time part to the default value 00 00 00 if either startdate or enddate have only a time part and the other only a datepart , datediff sets the missing time and datepart to the default values if startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, datediff sets the missing parts of the other to 0 syntax datediff ( datepart, startdate, enddate) argument data type description datepart char the units in which the function returns the difference between the startdate and enddate commonly used datepart units include month or second the datepart value cannot be specified in a variable, or in a quoted string like 'month' if datepart has a date value, and startdate and enddate are both assigned a time value, then the function returns 0 see the datepart values table for a list of supported arguments startdate time , timestamp , date a starting date or time value to determine the difference from the enddate value if only a time value is assigned, datediff sets the value of the missing datepart to the default value 1900 01 01 enddate time , timestamp , date an ending date or time value to determine the difference from the startdate value if only a time value is assigned, datediff sets the value of the missing datepart to the default value 1900 01 01 datepart abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns for millisecond , the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes, and 23 647 seconds for second , the maximum difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds each specific datepart name and abbreviations for that datepart name return the same value example the example shows how datediff calculates the difference between the two values based on the various datepart values select 'year', datediff(year, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'quarter', datediff(quarter, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'month', datediff(month, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'dayofyear', datediff(dayofyear, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'day', datediff(day, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'week', datediff(week, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'hour', datediff(hour, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'minute', datediff(minute, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000') 	union all select 'second', datediff(second, '2004 05 31 23 59 59 9999999', '2006 01 01 00 00 00 0000000'); output day 580 quarter 7 year 2 dayofyear 580 month 20 hour 13897 week 83 minute 833761 second 50025601 related links time zone functions docid\ f8elldnuxgrzirili8c4p formatting functions docid\ g ia4znpotfx2kegcvv2r