ANSI Date and Timestamp function for those who like to type less

The tip I am about to give here is a very interesting Oracle SQL Date and Timestamp function. Also it is a new way to retrieve records filtered by date and timestamp. Oracle supports DATE and TIMESTAMP function that works very well if you want to type less and want to produce same results as normal date function. ANSI DATE and TIMESTAMP were introduced since Oracle 9i into Oracle.


Without typing a whole lengthy TO_DATE (’01-JAN-2015’, ‘YYYY-MM-DD’) you can retrieve records using below short syntax (up to day precision):

SELECT * FROM employee_master WHERE hire_date > DATE ‘2015-01-01’ ;
(here ‘2015-01-01’ is in ‘YYYY-MM-DD’ format)

Here 2015-01-01 means ‘2015-01-01 00:00:00’, so if you query with the given date all the records that has date on January 01, 2015 will be fetched irrespective of time.


TIMESTAMP can be used if you want timestamp precision while fetching records. Below syntax can be used for that:

SELECT * FROM employee_master WHERE hire_date > TIMESTAMP ‘2015-01-01 13:05:45’;

Try this out and tell it to your friends.

For more information on Timestamp you can read this official documentation here.

Did you enjoy this post? Then feel free to put your comments below. Also you can subscribe here to stay updated on latest posts of

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.