ansi literals

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy!

Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I do specify a format.

I simply use

date '2000-01-01'


I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use

date '2000-01-01' +
avg(txndate-date '2000-01-01')


Other nice literals are timestamps

timestamp 
'2000-01-01 00:00:00.000000000 Etc/GMT+0'
timestamp '2000-01-01 00:00:00'


less usefull, because unsupported as oracle datatype

time '00:00:00.000000000 +00:00'
time '00:00:00'


also intervals
interval '1' day


one more I want to mention
q'[let's quote this]'


all this I found by reading the doc, more than once !

Published by

Laurent Schneider

Oracle Certified Master

5 thoughts on “ansi literals”

  1. hmm the only temporal display format in Ansi SQL is the ISO-8601 which is “yyyy-mm-dd hh:mm:dd.sssss”, intresting is it not :)

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>