Thursday 14 June 2018

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.


Which means this
date '2018-06-10'

Is the same as
to_char('10-Jun-2018','DD-MON-YYYY')

And you'll never want to type the latter again.

I learned this little chestnut as a trainer of SQL, but what I didn't pick up, or have since forgotten, is this

timestamp '2018-06-10 14:33:41'

Thanks again, Connor, for adding to this thread.

ANSI dates
Here's some more info on ANSI dates. It's not lazy, Tanel, it's efficient ;p

Learning is a lifetime pursuit.

2 comments:

Ronald Hollak said...

Please note that using the DATE yyyy-mm-dd and TIMESTAMP 'yyyy-mm-dd hh24:mi:ss' formats are Date(time) LITERALS and are never subject to any NLS setting.

Using literals have no side-effects whatsoever i.e. wrong implicit conversion, or unused (date)indexes in SQL where clauses.

It is the (almost) the same when we use Numeric Literals in SQL, which uses the . (point) as decimal separator and never a comma.

Scott Wesley said...

Thanks for the info!