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
I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use
Other nice literals are timestamps
less usefull, because unsupported as oracle datatype
also intervals
one more I want to mention
all this I found by reading the doc, more than once !
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 !
Posted by Laurent Schneider at 11/28/2005 04:45:00 PM
Permalink : ansi literals
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
5 Comments:
Interesting.... How do I use it in a PL/SQL Procedure? Is it desirable?
you can use it in a plsql procedure, and it is shorter than to_char()
Sweet! Can you show an example of it inside a procedure please?
declare d date; begin d := date '1941-09-09'; end;
exec if (sysdate < date '2006-01-01') then null; end if
ok?
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 :)
Post a Comment
<< Home