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.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at email@example.com. Again I appreciate your visit. Hope to see you again and again!