Excel Add-In for Oracle Service Cloud

Build 24.0.9060

DATE Functions

CURRENT_DATE()

Returns the current date value. 

                  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. 

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

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

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

  • 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)
    • week (wk, ww)
    • weekday (dw)
    • dayofyear (dy, y)
    • day (dd, d)
    • hour (hh)
    • minute (mi, n)
    • second (ss, s)
    • millisecond (ms)
  • number: The number to be added.
  • date: The expression of the datetime data type.
  • dateformat: The optional output date format. 

                  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 ( datepart , startdate , enddate )

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

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

                  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

DATE_FORMAT(date,format)

Returns the date or timestamp in the format specified. This function mirrors the MySQL DATE_FORMAT function.

  • date: A date or timestamp string.
  • format: The specifier string of the desired output format. The list of supported format specifiers comes from the MySQL DATE_FORMAT function (see link to MySQL documentation above). 

					SELECT DATE_FORMAT('9/4/2021 3:11:53 AM','%h')
					-- Result: 03

DATEFROMPARTS(integer_year, integer_month, integer_day)

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

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

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

DATENAME(datepart , date)

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

  • 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), and nanosecond (ns).
  • date: The datetime expression. 

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

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

DATEPART(datepart, date [,integer_datefirst])

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

  • 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), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
  • date: The datetime string.
  • datefirst: The optional integer representing the first day of the week. The default is 7, Sunday. 

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

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

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

Returns the datetime value for the specified date parts.

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

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

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

Returns the datetime value for the specified date parts.

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

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

DATE_TRUNC(date, datepart)

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

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

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

DATE_TRUNC2(datepart, date, [weekday])

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

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

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

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

  • date: The datetime string that specifies the date. 

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

DAYNAME(date)

Returns the name of the day of the week of the specified date.

  • date: The datetime string that specifies the date. 

                    SELECT DAYNAME('8/18/2021');
                    -- Result: Wednesday

DAYOFMONTH(date)

Returns the day of the month of the given date part.
  • date: The datetime string that specifies the date. 

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

DAYOFWEEK(date)

Returns the day of the week of the given date part.
  • date: The datetime string that specifies the date. 

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

DAYOFYEAR(date)

Returns the day of the year of the given date part.
  • date: The datetime string that specifies the date. 

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

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

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

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

                  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

EXTRACT(date_part FROM date_column_name)

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

  • date_part: One of the following date components: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
  • date_column_name: The name of a date column in a table. 

                  SELECT EXTRACT(YEAR FROM DateColumn)
                  -- Result: 2021

FDWEEK(date)

Returns the first day of the week of the given date part.
  • date: The datetime string that specifies the date.
  • weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the week. 

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

          SELECT FDWEEK('02-08-2018', 1)
          --Result: 02/11/2018

FDMONTH(date)

Returns the first day of the month of the given date part.
  • date: The datetime string that specifies the date.
  • month to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the month. 

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

          SELECT FDMONTH('02-08-2018', 1) 
          --Result: 03/01/2018

FDQUARTER(date)

Returns the first day of the quarter of the given date part.
  • date: The datetime string that specifies the date.
  • quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the quarter. 

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

          SELECT FDQUARTER('05-08-2018',1)
          --Result: 07/01/2018

FILEMODIFIEDTIME(uri)

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

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

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

FROM_DAYS(datevalue)

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.

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

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

FROM_UNIXTIME(time, issecond)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.

  • time: The time stamp value from epoch time. Milliseconds are accepted.
  • issecond: Indicates the time stamp value is milliseconds to epoch time. 

                      SELECT FROM_UNIXTIME(1540495231, 1);
                      -- Result: 2018-10-25 19:20:31

                      SELECT FROM_UNIXTIME(1540495357385, 0);
                      -- Result: 2018-10-25 19:22:37

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. 

                  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.

In addition, GETUTCDATE can take an optional second parameter, a date and time that are converted to UTC. 

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

                  SELECT GETUTCDATE('2020/08/31 13:56:00')
                  --Result: '2020-08-31 17:56:00'

HOUR(date)

Returns the hour component from the provided datetime.

  • date: The datetime string that specifies the date. 

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

ISDATE(date, [date_format])

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

  • date: The datetime string.
  • date_format: The optional datetime format. 

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

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

LAST_WEEK()

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

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

LAST_MONTH()

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

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

LAST_YEAR()

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

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

LDWEEK(date)

Returns the last day of the provided week.

  • date: The datetime string.
  • weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the week. 

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

LDMONTH(date)

Returns the last day of the provided month.

  • date: The datetime string.
  • months to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the month. 

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

        SELECT LDMONTH('02-08-2020', 1)
        --Result: 03/31/2020

LDQUARTER(date)

Returns the last day of the provided quarter.

  • date: The datetime string.
  • quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the quarter. 

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

        SELECT LDQUARTER('02-02-2020',1)
        --Result: 06/30/2020

MAKEDATE(year, days)

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

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

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

MINUTE(date)

Returns the minute component from the provided datetime.

  • date: The datetime string that specifies the date. 

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

MONTH(date)

Returns the month component from the provided datetime.

  • date: The datetime string that specifies the date. 

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

QUARTER(date)

Returns the quarter associated with the provided datetime.

  • date: The datetime string that specifies the date. 

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

SECOND(date)

Returns the second component from the provided datetime.

  • date: The datetime string that specifies the date. 

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

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

Returns the datetime value for the specified date and time.

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

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

STRTODATE(string,format)

Parses the provided string value and returns the corresponding datetime.

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

				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. 

                  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. 

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

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

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

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

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

TO_DAYS(date)

Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.

  • date: The datetime string that specifies the date. 

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

WEEK(date)

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

  • date: The datetime string that specifies the date. 

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

YEAR(date)

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

  • date: The datetime string. 

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

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060