Firebird – date and time literals, conversion to ISO-8601 compatible strings
Posted by jpluimers on 2016/12/28
For my reference:
select 'Date literal values' as description
, (cast('now' as timestamp)) as nowTimeStamp
, (cast('today' as timestamp)) as todayTimeStamp
, (cast('tomorrow' as timestamp)) as tomorrowTimeStamp
, (cast('yesterday' as timestamp)) as yesterdayTimeStamp
, cast(cast('Now' as date) as varchar(10)) as nowDateCast
, replace(cast(cast('Now' as date) as varchar(10)), '-', '') nowDateIso8601
, cast(cast('Now' as time) as varchar(13)) as nowTimeCast
, replace(cast(cast('Now' as time) as varchar(13)), ':', '') nowTimeIso8601
, cast(cast('Now' as timestamp) as varchar(24)) as nowTimeStampCast
, substring(replace(replace(replace(cast(cast('Now' as timestamp) as varchar(24)), '-', '') , ' ', 'T'), ':', '') from 1 for 15) as nowTimeStampIso8601
from rdb$database
;
Output and transposed output:
DESCRIPTION NOWTIMESTAMP TODAYTIMESTAMP TOMORROWTIMESTAMP YESTERDAYTIMESTAMP NOWDATECAST NOWDATEISO8601 NOWTIMECAST NOWTIMEISO8601 NOWTIMESTAMPCAST NOWTIMESTAMPISO8601 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Date literal values 3-8-2016 8:45:12 3-8-2016 4-8-2016 2-8-2016 2016-08-03 20160803 08:45:12.3860 084512.3860 2016-08-03 08:45:12.3860 20160803T084512 DESCRIPTION Date literal values NOWTIMESTAMP 2016-08-03 8:45:12 TODAYTIMESTAMP 2016-08-03 TOMORROWTIMESTAMP 2016-08-04 YESTERDAYTIMESTAMP 2016-08-02 NOWDATECAST 2016-08-03 NOWDATEISO8601 20160803 NOWTIMECAST 08:45:12.3860 NOWTIMEISO8601 084512.3860 NOWTIMESTAMPCAST 2016-08-03 08:45:12.3860 NOWTIMESTAMPISO8601 20160803T084512
References:
- How do convert or display the date or time as string?
- date -> varchar(10)
- time -> varchar(13)
- timestamp -> varchar(24)
- Firebird Date Literals
- now, today, tomorrow, yesterday
- how to get today date in YYYYMMDD in firebird – StackOverflow
- Internal functions → SUBSTRING()
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment