For one of our projects I work with time series data stored in an Oracle database, so I write a lot of SQL queries involving dates and timestamps. Most Oracle SQL queries I came across online use the TO_DATE function to specify date and time literals within queries:
SELECT * FROM events WHERE created >= TO_DATE('2012-04-23 16:30:00', 'YYYY-MM-DD HH24:MI:SS')
So this is what I started using as well. Of course, this is very flexible, because you can specify exactly the format you want to use. On the other hand it is very verbose.
From other database systems like PostgreSQL databases I was used to specify dates in queries as simple string literals:
SELECT * FROM events WHERE created BETWEEN '2018-01-01' AND '2018-01-31'
This doesn’t work in Oracle, but I was happy find out that Oracle supports short date and timestamp literals in another form:
SELECT * FROM events WHERE created BETWEEN DATE'2018-01-01' AND DATE'2018-01-31'
SELECT * FROM events WHERE created > TIMESTAMP'2012-04-23 16:30:00'
These date/time literals where introduced in Oracle 9i, which isn’t extremely recent. However, since most online tutorials and examples seem to use the TO_DATE function, you may be happy to find out about this little convenience just like me.
One thought on “Oracle database date and time literals”
Thanks for sharing!