STODT( ) function

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

Syntax

STODT(serial_datetime <,start_date>)

Parameters

Name	Type	Description
serial_datetime	numeric	The field, expression, or literal value to convert. Serial datetime values with the date and time portions separated by a decimal point are required. For example, 42003.75000
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_datetime

numeric

The field, expression, or literal value to convert.

Serial datetime values with the date and time portions separated by a decimal point are required. For example, 42003.75000

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 datetime value is output using the current Analytics date and time display formats.

Examples

Basic examples

Unadjusted start dates

Returns `20141231t060000` displayed as 31 Dec 2014 06:00:00 AM assuming current Analytics date and time display formats of DD MMM YYYY and hh:mm:ss PM:

STODT(42003.25000)

Returns `20141231t191530` displayed as 31 Dec 2014 07:15:30 PM assuming current Analytics date and time display formats of DD MMM YYYY and hh:mm:ss PM:

STODT(42003.802431)

Adjusted start dates

Returns `20181231t120000` displayed as 31 Dec 2018 12:00:00 PM assuming current Analytics date and time display formats of DD MMM YYYY and hh:mm:ss PM:

STODT(42003.50000, `19040101`)

Fields as input

Returns the equivalent datetime for each serial datetime value in the Receipt_datetime field:

STODT(Receipt_datetime)

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 datetime 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 `20131231t180000`):

STODT(42003.75000) - 365

Remarks

How it works

The STODT( ) function allows you to convert serial datetimes to regular datetimes. Analytics serial datetimes represent the number of days that have elapsed since 01 January 1900, and following the decimal point, represent a fractional portion of 24 hours, with 24 hours equaling 1.

Serial datetime	Regular datetime equivalent
1.25	02 January 1900 06:00:00 AM
365.75000	31 December 1900 06:00:00 PM
42003.79167	31 December 2014 07:00:00 PM
42003.802431	31 December 2014 07:15:30 PM
42003.00000	31 December 2014 12:00:00 AM
42003.50000	31 December 2014 12:00:00 PM
0.0	not valid

For more information about serial datetimes, 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 datetimes are calculated.

Start date in source data file	Specify:	Details
01 January 1900	STODT(datetime_field)	You do not need to specify a start_date, because 01 January 1900 is the default start date.
01 January 1901	STODT(datetime_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	STODT(datetime_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 STODT( ) function.

Other datetime conversion functions

Serial to Datetime conversion

Function	Description
STOD( )	Converts a serial date – that is, a date expressed as an integer – to a date value. Abbreviation for "Serial to Date".
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