You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




28 November 2005

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 !

5 Comments:

Anonymous Colin said...

Interesting.... How do I use it in a PL/SQL Procedure? Is it desirable?

29/11/05 06:09  
Blogger Laurent Schneider said...

you can use it in a plsql procedure, and it is shorter than to_char()

29/11/05 09:07  
Anonymous Colin said...

Sweet! Can you show an example of it inside a procedure please?

29/11/05 15:40  
Blogger Laurent Schneider said...

declare d date; begin d := date '1941-09-09'; end;

exec if (sysdate < date '2006-01-01') then null; end if

ok?

29/11/05 16:15  
Blogger Örjan Bernt Lundberg said...

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 :)

1/12/05 17:37  

Post a Comment

<< Home