The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,733 other followers

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:

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: