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

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 datetimes are calculated. If this parameter is omitted, the default start date of 01 January 1900 is used.

Output

Datetime. The datetime value is output using the current ACL date and time display formats.

Remarks

The STODT( ) function allows you to convert serial datetimes to regular datetimes. ACL 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

Note

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

Both ACL 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.

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

If you have a source data file with serial datetimes that use a start date more recent than 01 January 1900, you can use the start_date parameter to match the start date in the source data. You cannot specify a start_date earlier than 01 January 1900. If you need to match 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.

Examples

Example Return value

STODT(42003.25000)

31 Dec 2014 06:00:00 AM

Assumes current ACL date and time display formats of DD MMM YYYY and hh:mm:ss PM

STODT(42003.50000, `19040101`)

31 Dec 2018 12:00:00 PM

Assumes current ACL date and time display formats of DD MMM YYYY and hh:mm:ss PM

STODT(42003.75000) - 365

31 Dec 2013 06:00:00 PM

Return values adjusted to match serial datetime data that uses a start date of 01 Jan 1899

Assumes current ACL date and time display formats of DD MMM YYYY and hh:mm:ss PM

STODT(42003.802431)

31 Dec 2014 07:15:30 PM

Assumes current ACL date and time display formats of DD MMM YYYY and hh:mm:ss PM

STODT(Receipt_timestamp)

The equivalent datetime for each serial datetime value in the Receipt_timestamp field

Related reference
STOD( ) function
STOT( ) function


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback