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) 99,188,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select add months('2022 11 15 04 18 00',2); output 2023 01 15 century returns the number of centuries syntax century(time) 101,185,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ hem zd qwquv8dfnvnqz8 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 docid\ hem zd qwquv8dfnvnqz8 date trunc returns the date or timestamp entered, truncated to the specified precision syntax date trunc(precision, time) 125,147,376 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ hem zd qwquv8dfnvnqz8 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) 109,177,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 101,267,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 111,164,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 115,160,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example this example uses current date set to 2023 01 03 select decade(current date()); output 202 dow alias for docid\ hem zd qwquv8dfnvnqz8 doy alias for docid\ hem zd qwquv8dfnvnqz8 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]) 119,162,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 114,168,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select epoch(current timestamp()); output 1672743901 extract extract a component from a timestamp or date syntax extract(precision from time) 130,148,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 115,144,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 110,154,377 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 111,155,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select isdate('2023 01 03'); output true makedatetime returns a timestamp consisting of the specified date and time syntax makedatetime(date, time) 112,153,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 141,138,376 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,131,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,145,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 114,149,377 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,133,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,139,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,109,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,137,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select next day('2023 02 27', 'sat'); output 2023 03 04 now alias for docid\ hem zd qwquv8dfnvnqz8 quarter returns an integer between 1 and 4 representing the quarter of the year in which the specified date falls syntax quarter(time) 140,133,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ hem zd qwquv8dfnvnqz8 the function rounds up values to the specified precision syntax round(time, precision) 125,147,376 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,129,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,133,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ naengoslrgk2ikpyh6lkc 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) 140,134,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 https //en wikipedia org/wiki/iso week date definition syntax week(time) 140,136,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,143,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,131,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type trueleft unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 140,141,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type trueleft unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ iwtcunl3t9rlghwvu2dsx docid\ naengoslrgk2ikpyh6lkc