Laurent Schneider

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 !

This entry was posted on Monday, November 28th, 2005 at 16:45 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

Colin Says:
November 29th, 2005 at 06:09
Interesting…. How do I use it in a PL/SQL Procedure? Is it desirable?
Laurent Schneider Says:
November 29th, 2005 at 09:07
you can use it in a plsql procedure, and it is shorter than to_char()
Colin Says:
November 29th, 2005 at 15:40
Sweet! Can you show an example of it inside a procedure please?
Laurent Schneider Says:
November 29th, 2005 at 16:15
declare d date; begin d := date ‘1941-09-09′; end;

exec if (sysdate
Örjan Bernt Lundberg Says:
December 1st, 2005 at 17:37
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

Use <code> and </code> to post code