STOD( ) function

Converts a serial date – that is, a date expressed as an integer – to a date value. Abbreviation for "Serial to Date".

Syntax

STOD(serial_date <,start_date>)

Parameters

Name	Type	Description
serial_date	numeric	The field, expression, or literal value to convert. serial_date can be a serial date or a serial datetime. Only the date portion of serial datetimes is considered. The time portion is ignored.
start_date optional	datetime	The start date from which serial dates are calculated. If omitted, the default start date of 01 January 1900 is used.

Name

Type

Description

serial_date

numeric

The field, expression, or literal value to convert.

serial_date can be a serial date or a serial datetime. Only the date portion of serial datetimes is considered. The time portion is ignored.

start_date

optional

datetime

The start date from which serial dates are calculated. If omitted, the default start date of 01 January 1900 is used.

Output

Datetime. The date value is output using the current Analytics date display format.

Examples

Basic examples

Returns `20141231` displayed as 31 Dec 2014 assuming a current Analytics date display format of DD MMM YYYY:

STOD(42003)

Returns `20181231` displayed as 31 Dec 2018 assuming a current Analytics date display format of DD MMM YYYY:

STOD(42003, `19040101`)

Returns the equivalent date for each serial date value in the Invoice_Date field:

STOD(Invoice_Date)

Advanced examples

Adjusting for a start date before 1900-01-01

Use date arithmetic to adjust the start date to a value that is earlier than the Analytics minimum date of January 1, 1900:

Convert the serial date using the default start date.
Subtract the number of days before 1900-01-01 that the actual start date falls.

To use 1899-01-01 as the start date (evaluates to `20131231`):

STOD(42003) - 365

Remarks

How it works

The STOD( ) function allows you to convert serial dates to regular dates. Analytics serial dates represent the number of days that have elapsed since 01 January 1900.

Serial date	Regular date equivalent
1	02 January 1900
365	31 December 1900
42003	31 December 2014
0	not valid

For more information about serial dates, see Serial datetimes.

Analytics serial dates compared to Excel serial dates

Analytics serial dates are similar to Microsoft Excel serial dates. You should be aware of one key point of similarity and one key point of difference. The two points are unrelated.

Point of similarity

Both Analytics and Excel treat the year 1900 as a leap year, with 366 days. Although 1900 was not in fact a leap year, Excel treated it as one in order to maintain compatibility with Lotus 1-2-3.

Point of difference

Analytics serial dates are offset from Excel serial dates by one day. In Excel, 01 January 1900 has a serial date of '1'. In Analytics, 01 January 1900 is not counted, and 02 January 1900 has a serial date of '1'.

The start_date

Some source data files may use a start date other than 01 January 1900. The start_date allows you to match the start date in a source data file. The start date is the date from which serial dates are calculated.

Start date in source data file	Specify:	Details
01 January 1900	STOD(date_field)	You do not need to specify a start_date, because 01 January 1900 is the default start date.
01 January 1901	STOD(date_field, `19010101`)	You specify a start_date of `19010101` to match the start date of 01 January 1901 used in the source data file.
01 January 1899	STOD(date_field) - 365	You cannot specify a start_date earlier than 01 January 1900. If a source data file uses a start date earlier than 01 January 1900, you can create a datetime expression that subtracts an appropriate number of days from the output results of the STOD( ) function.

Other datetime conversion functions

Serial to Datetime conversion

Function	Description
STODT( )	Converts a serial datetime – that is, a datetime expressed as an integer, and a fractional portion of 24 hours – to a datetime value. Abbreviation for "Serial to Datetime".
STOT( )	Converts a serial time – that is, a time expressed as a fractional portion of 24 hours, with 24 hours equaling 1 – to a time value. Abbreviation for "Serial to Time".

Character or Numeric to Datetime conversion

Function	Description
CTOD( )	Converts a character or numeric date value to a date. Can also extract the date from a character or numeric datetime value and return it as a date. Abbreviation for "Character to Date".
CTODT( )	Converts a character or numeric datetime value to a datetime. Abbreviation for "Character to Datetime".
CTOT( )	Converts a character or numeric time value to a time. Can also extract the time from a character or numeric datetime value and return it as a time. Abbreviation for "Character to Time".

Datetime to Character conversion

Function	Description
DATE( )	Extracts the date from a specified date or datetime and returns it as a character string. Can also return the current operating system date.
DATETIME( )	Converts a datetime to a character string. Can also return the current operating system datetime.
TIME( )	Extracts the time from a specified time or datetime and returns it as a character string. Can also return the current operating system time.

[ Back to top ]

ACL Scripting Guide 14.1