Date and Time Functions

Date and time functions allow you to manipulate dates and times within Spectrum Workbooks. These include seeing if a specific date is before another, showing dates as timestamps, returning only the month of a specified date, etc.

INFO

Three character month abbreviation (space) one or two digit day (comma)(space) four digit year (space) two digit hour (colon) two digit minute (colon) two digit second (space) two character meridiem notation.

(e.g., 12.01.2012 gets transferred to Jan 12, 2012 12:00:00 AM)

When selecting a pattern, you select the way the data gets parsed (your input format, this can be connected to your systems representation of dates and times).

INFO

Spectrum always uses the following format:

Three character month abbreviation (space) two digit day (comma)(space) four digit year (space) two digit hour (colon) two digit minute (colon) two digit second (space) two character meridiem notation.

Example

12.01.2012 gets transferred to Jan 12, 2012 12:00:00 AM)

When selecting a pattern, you select the way the data gets parsed (your input format, this can be connected to your systems representation of dates and times).

ADDTODATE#

Syntax#

ADDTODATE(<date>;<string,using date constant>)

Description#

Adds (or subtracts) time to a date using an integer and one date constant.

Time units

Time Unit	Constant
Year	y
Quarter	q
Month	M
Week	w
Day	d
Hour	h
Minute	m
Second	s
Millisecond	ms

Examples

Date	String Used	ADDTODATE Result
Oct 15, 2002 04:36:34 AM	5y	Oct 15, 2007 04:36:34 AM
Oct 15, 2002 04:36:34 AM	3M	Jan 15, 2003 04:36:34 AM
Oct 15, 2002 04:36:34 AM	5000s	Oct 15, 2002 06:29:54 PM
Oct 15, 2002 04:36:34 AM	-5000s	Oct 14, 2002 02:43:14 PM

This function can be used to subtract time away from a date by using the method above with a minus sign "-" before the integer and constant. The function respects Daylight Saving Time (DST). Depending on the date "-1d" could also subtract 23 or 25 hours when used with this function.

Additional Information#

Make Date Functions Aware of Daylight Savings Time#

To make a function aware of daylight savings time (DST) use ADDTODATE in order to add or subtract time from a date.

Example

=EXPAND_DATE_RANGE(ADDTODATE(TODAY();"-5000d");TODAY();1d)

This is also recommended when working with Partitioned Data and Advanced Partition Filter.

Example

In order to catch the last seven days of the previous month in a Workbook using a formula that repeats daylight savings time.

$partition < ENDOFMONTH(STARTOFMONTH(ADDTODATE(TODAY();"-1M"))) && $partition >= ENDOFMONTH(STARTOFMONTH(ADDTODATE(TODAY();"-1M")))-7d

AFTER#

Syntax#

AFTER(<date to check>;<reference date>)

Description#

AFTER compares two dates and determines whether the date in Column1 is after the date in Column2. This function returns a Boolean value, either true or false.

Examples

Column1	Column2	AFTER Returns
Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34	true
Aug 31, 2011 11:30:47 AM	Aug 31, 2011 11:30:47 AM	false
Apr 12, 1978 12:05:08 PM	Sep 1, 2009 9:18:36	false

ASDATE#

Syntax#

ASDATE(<date string>;<optional data parse pattern>)

Description#

Converts a character sequence to a date using either a predefined (dd-MM-yyyy hh:mm:ss, dd.MM.yyyy hh:mm:ss, dd-MM-yyyy, dd.MM.yyyy) or a custom date parse pattern.

For further details about date and time parse patterns please refer to Using Date and Time Patterns.

Examples

Column1	Date Parse Pattern	ASDATE Returns
19-01-2008 11:13:27	dd-MM-yyyy hh:mm:ss	Sep, 19 2008 11:13:27 AM
25.09.05 12:23:05	dd.MM.yy hh:mm:ss	Sep, 25 2005 12:23:05 PM
15-10-2002	dd-MM-yyyy	Oct, 15 2002 12:00:00 AM
06.11.2010	dd.MM.yyyy	Nov, 25 2010 12:00:00 AM
2011-05-04	yyyy-MM-dd	May, 04 2011 12:00:00 AM
2009-06-30T18:30:00.001	yyyy-MM-dd'T'HH:mm:ss.SSS	Jun 30, 2009 06:30:00 PM

BEFORE#

Syntax#

BEFORE(<date to check>;<reference date>)

Description#

BEFORE compares two dates and determines whether the date in Column1 is before the date in Column2. This function returns a Boolean value, either true or false.

Examples

Column1	Column2	BEFORE Returns
Apr 12, 1978 12:05:08 PM	Sep 1, 2009 9:18:36	true
Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34	false
Aug 31, 2011 11:30:47 AM	Aug 31, 2011 11:30:47 AM	false

CEILINGDATE#

Syntax#

CEILINGDATE(<date>;<string>)

Description#

Rounds a date argument up to the beginning of the next date interval. The size of the date interval is indicated by a string argument (supported interval arguments are 'YEAR', 'QUARTER', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', or 'MILLISECOND').

Examples

Date	String	CEILINGDATE Returns
Jun 14, 2018 12:23:05	YEAR	Jan 1, 2019 12:00:00
Jun 14, 2018 12:23:05	QUARTER	Jul 1, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	MONTH	Jul 1, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	WEEK	Jun 17, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	DAY	Jun 15, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	HOUR	Jun 14, 2018 01:00:00 PM
Jun 14, 2018 12:23:05	MINUTE	Jun 14, 2018 12:24:00 PM
Jun 14, 2018 12:23:05.001	SECOND	Jun 14, 2018 12:23:06 PM
Jun 14, 2018 12:23:05.001	MILLISECOND	Jun 14, 2018 12:23:05.001 PM

CRON_MATCHER#

Syntax#

CRON_MATCHER(<pattern>;<date>)

INFO: The pattern can't be referenced from an existing column.

Description#

Checks if the given date matches the given cron expression.

Examples

Pattern	Date	CRON_MATCHER() returns
* * * * *	01/10/2012 10:10	true
10 10 * * *	01/10/2012 10:10	true
10 11 * * *	01/10/2012 10:10	false

DAY#

Syntax#

DAY(<day>)

Description#

Returns the day of the month of a date as an integer, ranging from 1 to 31.

Examples

Column1	DAY returns
Aug 2, 2011 3:55:03 PM	2
Aug 31, 2011 11:30:47 AM	31
Apr 12, 1978 12:05:08 PM	12

DAYOFYEAR#

Syntax#

DAYOFYEAR(<date>)

Description#

Returns the day of the year for the supplied date in a range from 1 to 366.

Examples

Date	DAYOFYEAR Returns
Jan 1, 2018 12:23:05 PM	1
Jun 14, 2018 12:23:05 PM	165
Dec 25, 2018 12:23:05 PM	359

ENDOFMONTH#

Syntax#

ENDOFMONTH(<date>)

Description#

Returns the date of the last day of month (ending in the evening at 11:59:59.999 PM / 23:59:59.999) for the given date.

Examples

Date	ENDOFMONTH returns
Oct 15, 2002 04:36:34	Oct 31, 2002 11:59:59 PM
Apr 17, 2012 07:27:12	Apr 30, 2012 11:59:59 PM
Jul 5, 1979 11:59:59	Jul 31, 1979 11:59:59 PM

ExcelConverter#

Syntax#

ExcelConverter(<integer>)

Description#

Converts integer dates from Excel to a date.

Examples

Excel formula	Excel result	ExcelConverter Returns
=DATEVALUE("2011-08-22“)	40777	Aug 22, 2011 12:00:00 AM
=DATEVALUE("2011/02/23“)	40597	Feb 23, 2011 12:00:00 AM
=DATEVALUE("5-JUL")	42190	Jul 5, 2015 12:00:00 AM

EXPAND_DATE_RANGE#

Syntax#

EXPAND_DATE_RANGE(<range_start:date>;<range_end:date>[;<interval:integer> or time interval])

Description#

Expands a date range into multiple rows, one for each interval, one day by default. The generated series of dates starts with the range_start parameter and end with the range_end parameter (if applicable) and the series is sorted based on the relationship between these parameters: If range_start is before range_end, the series will run forward in time from range_start. If range_start is after range_end, the series will run backwards in time from range_start. The interval parameter defaults to one day and can be changed in the field using an integer set in milliseconds (ms) or a time interval (10d, 1h, 30s, etc.)

INFO: Results must have a record count lower or equal to 100,000 rows.

Example

Given the following data:

Date_1	Date_2
Jan 1, 2012	Jan 15, 2012

Then use the function:

EXPAND_DATE_RANGE(#Workbook!Date_1;#Workbook!Date_2)

The result of the function is:

EXPAND_DATE_RANGE returns
Jan, 1, 2012 12:00:00 AM
Jan, 2, 2012 12:00:00 AM
Jan, 3, 2012 12:00:00 AM
Jan, 4, 2012 12:00:00 AM
Jan, 5, 2012 12:00:00 AM
Jan, 6, 2012 12:00:00 AM
Jan, 7, 2012 12:00:00 AM
Jan, 8, 2012 12:00:00 AM
Jan, 9, 2012 12:00:00 AM
Jan, 10, 2012 12:00:00 AM
Jan, 11, 2012 12:00:00 AM
Jan, 12, 2012 12:00:00 AM
Jan, 13, 2012 12:00:00 AM
Jan, 14, 2012 12:00:00 AM
Jan, 15, 2012 12:00:00 AM

FLOORDATE#

Syntax#

FLOORDATE(<date>;<string>)

Description#

Rounds a date argument down to the beginning of the current date interval. The size of the date interval is indicated by a string argument (supported interval arguments are 'YEAR', 'QUARTER', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', or 'MILLISECOND').

Examples

Date	String	FLOORDATE Returns
Jun 14, 2018 12:23:05	YEAR	Jan 1, 2018 12:00:00
Jun 14, 2018 12:23:05	QUARTER	Apr 1, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	MONTH	Jun 1, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	WEEK	Jun 10, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	DAY	Jun 14, 2018 12:00:00 AM
Jun 14, 2018 12:23:05	HOUR	Jun 14, 2018 12:00:00 PM
Jun 14, 2018 12:23:05	MINUTE	Jun 14, 2018 12:23:00 PM
Jun 14, 2018 12:23:05.001	SECOND	Jun 14, 2018 12:23:05 PM
Jun 14, 2018 12:23:05.001	MILLISECOND	Jun 14, 2018 12:23:05.001 PM

FORMATDATE#

Syntax#

FORMATDATE(<date>;<string>)

Description#

Converts dates to strings by applying a date and time parse pattern.

Examples

Column1	Pattern	FORMATDATE returns
19 Sep, 2008 11:13:27	dd-MM-yyyy hh:mm:ss	19-09-2008 11:13:27
25 Sep, 2005 12:23:05 PM	dd.MM.yyyy hh:mm:ss	25.09.05 12:23:05
15 Oct, 2002 10:35:08	dd-MM-yyyy	15-10-2002
25 Nov, 2010 11:56:13	yyyy-MM-dd	2010-11-25

HOUR#

Syntax#

HOUR(<date>)

Description#

Returns only the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Examples

Column1	HOUR returns
Aug 2, 2011 3:55:03 PM	15
Aug 31, 2011 11:30:47 AM	11
Apr 12, 1978 12:05:08 PM	12

MIDNIGHT#

Syntax#

MIDNIGHT(<date>)

Description#

Returns the given date with a time of midnight (12:00:00 am).

Examples

Column1	MIDNIGHT Returns
Aug 2, 2011 03:55:03 PM	Aug 2, 2011 12:00:00 AM
Jul 2, 2011 03:55:09 PM	Jul 2, 2011 12:00:00 AM
Jun 2, 2011 03:55:00 PM	Jun 2, 2011 12:00:00 AM
May 2, 2011 03:55:55 PM	May 2, 2011 12:00:00 AM
Apr 2, 2011 03:55:58 PM	Apr 2, 2011 12:00:00 AM

MILLISECOND#

Syntax#

MILLISECOND(<date>)

Description#

Truncates a date to only display the milliseconds associate with the date.

Examples

Date	MILLISECOND returns
Oct 15, 2002 4:34:34.125	125
Sep 26, 2012 12:33:25.746	746
Jan 08, 2013 11:22:44.012	012
Sep 01, 2009 5:56:17	0

MINUTE#

Syntax#

MINUTE(<date>)

Description#

Returns only the minute of a time value. The minute is given as an integer, ranging from 0 to 59.

Examples

Column1	MINUTE returns
Aug 2, 2011 3:55:03 PM	55
Aug 31, 2011 11:30:47 AM	30
Apr 12, 1978 12:05:08 PM	05

MONTH#

Syntax#

MONTH(<date>)

Description#

Returns only the month of a date represented as an integer from 1 to 12.

Examples

Column1	MONTH returns
May 2, 2011 3:55:03 PM	5
Aug 31, 2011 11:30:47 AM	8
Apr 12, 1978 12:05:08 PM	4

MSTODATE#

Syntax#

MSTODATE(<number>)

Description#

Converts an integer representing a timestamp in milliseconds from January 1, 1970, 00:00:00 GMT to its corresponding date value. Spectrum timestamps are similar to Unix Time, except they are based on milliseconds instead of seconds.

Examples

Column1	MSTODATE returns
1,287,110,074,001	Oct 15, 2010 4:34:34 AM
1,285,497,205,000	Sep 26, 2010 12:33:25 PM
1,314,783,045,000	Auf 31, 2011 11:30:45 AM

NETWORKDAYS#

Syntax#

NETWORKDAYS(<date>;<date>;[holidays])

Description#

Using the ‘NETWORKDAYS’ function returns the number of whole working days between a start date and an end date. Working days exclude weekends and any dates that are identified as holidays.

‘NETWORKDAYS’:

returns null in case a date is missing
does not include weekend days
displays a negative amount if the end date is before the start date
subtracts all holidays when they are marked with the holiday variable in a single column
does not subtract holidays when they are on a weekend

Examples

Example Without Holidays

Given the following date:

August 23rd in 2021 to August 24th in 2021 = 2 days, thereof 0 days weekend, thereof 0 days holidays

Formula applied:

NETWORKDAYS(#Start_Date;#End_Date)

Start_Date	End_Date	NETWORKDAYS
Aug 23, 2021	Aug 24, 2021	2

Example With Holidays

Given the following date:

August 23rd in 2021 to August 29th in 2021 = 7 days, thereof 2 days weekend, thereof 2 days holidays on Aug, 24th and Aug, 29th

Formula applied:

NETWORKDAYS(#Start_Date;#End_Date;[#Holiday_1,#Holiday_2])

Start_Date	End_Date	Holiday_1	Holiday_2	NETWORKDAYS
Aug 23, 2021	Aug 29, 2021	Aug 24,2021	Aug 29, 2021	4

NOW#

Syntax#

NOW()

Description#

Returns the current date and time when the Workbook is run.

Examples

Current time when job is run	NOW returns
Fri 20 May 11:16	May 20, 2016 11:06:03 AM
Wed 8 June 14:24	Jun 8, 2016 02:24:43 PM

QUARTER#

Syntax#

QUARTER(<date>)

Description#

Returns the quarter of the year for the supplied date in a range from 1 to 4.

Defined Quarters

Quarter	Months
1	January, February, March
2	April, May, June
3	July, August, September
4	October, November, December

Examples

Date	QUARTER Returns
Jan 06, 2018 12:23:05	1
Apr 06, 2018 12:23:05	2
Jul 06, 2018 12:23:05	3
Oct 06, 2018 12:23:05	4

ROUNDTIME#

Syntax#

ROUNDTIME(<date>;<string, using a time constant>)

Description#

Rounds a date field down to the nearest (s)econd, (m)inute, (h)our, or (d)ay set by a time constant.

Time constants for ROUNDTIME:

Time unit	Constant
Day	d
Hour	h
Minute	m
Second	s

Limitation#

This function can't be applied to an empty value. If a column contains empty values, consider adding a check for this, e.g.

IF(ISBLANK(#DateColumn); null; ROUNDTIME(#DateColumn; "1d"))

Examples

Date	Time constant	ROUNDTIME returns
Jan 12, 2013 08:33:12	7d	Jan 7, 2013 12:00:00 AM
Jan 12, 2013 08:33:12	3h	Jan 12, 2013 06:00:00 AM
Jan 12, 2013 08:33:12	10m	Jan 12, 2013 08:30:00 AM
Jan 12, 2013 08:33:12	5s	Jan 12, 2013 08:33:10 AM

SECOND#

Syntax#

SECOND(<date>)

Description#

Truncates a date to only display the seconds associate with the date.

Examples

Date	SECOND returns
Oct 15, 2002 4:34:34	34
Sep 26, 2012 12:33:25	25
Jan 08, 2013 11:22:44	44
Sep 01, 2009 5:56	0

SHIFTTIMEZONE#

Synta#

SHIFTTIMEZONE(<date>;<Time Zone 1>;<Time Zone 2>)

Description#

This function allows you to assign a time zone to values in a date type column and then shift the values to a different time zone. is the time zone you want for the final values, is the current time zone for the values being shifted. Time Zone 1 is usually the time zone of the Spectrum application server or the user's time zone (if different).

INFO

Information on time and date data within Spectrum pertaining to timezones:

Spectrum internally stores all dates as universal time (UTC)
Spectrum displays dates within Workbooks as a local time for your Spectrum instance
Spectrum recognizes the time zone for dates with an included parsed time zone
Spectrum assigns your local instance time zone to dates parsed without a time zone

INFO

Time zones available for the function's arguments can be found at http://joda-time.sourceforge.net/timezones.html.

Example 1

Spectrum application server runs in the CET time zone (Berlin). A user located in Berlin, Germany has pulled in log files from a server located in Los Angeles, USA. The server logs all data using the local time zone but the dates extracted from the log files have no assigned time zone stamp. Because there is no time zone stamp, the user's machine interprets the log entries as happening in their local Berlin time. The SHIFTTIMEZONE function lets the user correct this by declaring the time zone they want for analysis "Europe/Berlin" (Time Zone 1) and the time zone that the data is currently in "America/Los_Angeles" (Time Zone 2).

Date	#Log_Time	Clarification (not available in Spectrum UI)
Time Zone 1	Europe/Berlin	The time zone you want for final analysis
Time Zone 2	America/Los_Angeles	The current time zone for values being shifted

Log_Time Raw data (events occurred during Pacific time)	SHIFTTIMEZONE(#Log_Time;"America/Los_Angeles";"UTC")
Jul 15, 2019 03:00:00 AM	Jul 15, 2019 12:00:00
Jul 15, 2019 04:00:00 AM	Jul 15, 2019 01:00:00
Jul 15, 2019 05:00:00 AM	Jul 15, 2019 02:00:00
Jul 15, 2019 06:00:00 AM	Jul 15, 2019 03:00:00

Example 2

A server registering events occurring in Los Angeles, USA is set to use UTC time zone and has not been configured to record any time zone in log entries. A user has pulled in log files from this server. The dates extracted from the log files have no time zone stamp assigned and represent UTC time. The SHIFTTIMEZONE function lets the user correct this by declaring the time zone they want for analysis "America/Los_Angeles " (Time Zone 1) and the time zone that the data is currently in "UTC" (Time Zone 2).

Date	#Log_Time	Clarification (not available in Spectrum UI)
Time Zone 1	America/Los_Angeles	The time zone you want for final analysis
Time Zone 2	UTC	The current time zone for values being shifted

Log_Time Raw data (events occurred during Pacific time)	SHIFTTIMEZONE(#Log_Time;"America/Los_Angeles";"UTC")
Jul 15, 2019 03:00:00 AM	Jul 14, 2019 07:00:00 PM
Jul 15, 2019 04:00:00 AM	Jul 14, 2019 08:00:00 PM
Jul 15, 2019 05:00:00 AM	Jul 14, 2019 09:00:00 PM
Jul 15, 2019 06:00:00 AM	Jul 15, 2019 01:00:00 AM

STARTOFMONTH#

Syntax#

STARTOFMONTH(<date>)

Description#

Returns the date of the first day of month (starting the morning at 00:00:00 / 12:00:00 AM) for the given date.

Examples

Date	STARTOFMONTH returns
Oct 15, 2002 04:36:34	Oct 1, 2002 12:00:00 AM
Apr 17, 2012 07:27:12	Apr 1, 2012 12:00:00 AM
Jul 5, 1979 11:59:59	Jul 1, 1979 12:00:00 AM

STARTOFWEEK#

Syntax#

STARTOFWEEK(<date>)

Description#

Returns the date of the first day of week (Sunday night/Monday morning 00:00:00) for the given date.

Examples

Date	STARTOFWEEK returns
Jan 1, 2012	Dec 26, 2011 12:00:00 AM
Jan 2, 2012	Jan 2, 2012 12:00:00 AM
Jan 3, 2012	Jan 2, 2012 12:00:00 AM
Jan 4, 2012	Jan 2, 2012 12:00:00 AM
Jan 5, 2012	Jan 2, 2012 12:00:00 AM
Jan 6, 2012	Jan 2, 2012 12:00:00 AM
Jan 7, 2012	Jan 2, 2012 12:00:00 AM
Jan 8, 2012	Jan 2, 2012 12:00:00 AM
Jan 9, 2012	Jan 9, 2012 12:00:00 AM
Jan 10, 2012	Jan 9, 2012 12:00:00 AM
Jan 11, 2012	Jan 9, 2012 12:00:00 AM
Jan 12, 2012	Jan 9, 2012 12:00:00 AM
Jan 13, 2012	Jan 9, 2012 12:00:00 AM
Jan 14, 2012	Jan 9, 2012 12:00:00 AM
Jan 15, 2012	Jan 9, 2012 12:00:00 AM

TIMESTAMP#

Syntax#

TIMESTAMP(<date>)

Description#

Difference, measured in milliseconds, between the date value set and midnight, January 1, 1970 UTC.

Examples

Column1	TIMESTAMP returns
Oct 15, 2010 4:34:34 AM	1,287,110,074,001
Sep 26, 2010 12:33:25 PM	1,285,497,205,000
Auf 31, 2011 11:30:45 AM	1,314,783,045,000

TIMESTAMPDIFF#

Syntax#

TIMESTAMPDIFF(<string>;<date>;<date>)

Description#

Returns the number of whole date intervals between two date arguments. The size of the date interval is indicated by a string argument (supported interval arguments are 'YEAR', 'QUARTER', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', or 'MILLISECOND'). The interval is calculated by subtracting the first date argument (Date1) from the second (Date2). If Date2 is before Date1, the value returned by the function will be negative.

Examples

String	Date1	Date2	TIMESTAMPDIFF Returns
YEAR	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	7
YEAR	Jun 14, 2018 12:23:05 PM	Jun 14, 2011 12:23:05 PM	-7
QUARTER	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	28
MONTH	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	84
WEEK	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	365
DAY	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	2,557
HOUR	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	61,368
MINUTE	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	3,682,080
SECOND	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	7220,924,800
MILLISECOND	Jun 14, 2011 12:23:05 PM	Jun 14, 2018 12:23:05 PM	7220,924,800,000

TODAY#

Syntax#

TODAY()

Description#

Returns the current day (time at midnight) as a date.

Examples

Current date and time	TODAY returns
Oct 15, 2010 4:34:34 AM	Oct 15, 2010 12:00:00 AM
Sep 26, 2010 12:33:25 PM	Sep 26, 2010 12:00:00 AM
Aug 31, 2011 11:30:45 AM	Aug 31, 2011 12:00:00 AM

WEEKDAY#

Syntax#

WEEKDAY(<date>)

Description#

Returns an integer between (1-7) corresponding to the day of the week of the date.

Day	Integer
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5
Saturday	6
Sunday	7

Examples

Date	WEEKDAY returns
Oct 15, 2002	2
Oct 16, 2002	3
Oct 17, 2002	4
Oct 18, 2002	5
Oct 18, 2003	6
Oct 18, 2004	1
Oct 18, 2030	5

WEEKOFYEAR#

Syntax#

WEEKOFYEAR()

Description#

Returns the week of the year for the supplied date in a range from 1 to 53.

Examples

Date	WEEKOFYEAR Returns
Jan 1, 2018 12:23:05	1
Jun 14, 2018 12:23:05	24
Dec 25, 2018 12:23:05	52

YEAR#

Syntax#

YEAR()

Description#

Returns the year corresponding to a date. The year is returned as an integer, ranging from 1900 to 9999.

Examples

Current date and time	YEAR returns
Oct 15, 2002 4:34:34 AM	2002
Sep 26, 2010 12:33:25 PM	2010
Aug 31, 2011 11:30:45 AM	2011