How do I calculate the time between the current time in the database? Suppose the date in the database is:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
date 22/6 2001 11.30 AM 12.00 PM 4.00 AM date 23/6/2001 2.00 AM 4.00 AM 5.00 AM
...and the current time is 3.00AM on 23/6/2001. How can I calculate this? The answer should be 2:00AM in this case.
Let's do this two ways -- first, assuming that the dates and times are stored in a single datetime column (as is available in Microsoft SQL/Server), and second, that the dates and times are stored in separate date and time columns.
In the first case, the current date and time are provided as a single value by the SQL/Server GETDATE() function, so the query is really simple --
select max(theDateTime) from theTable where theDateTime <= getdate()
In the second case, with separate date and time columns, it's a bit trickier. Standard SQL provides two functions, CURRENT_DATE and CURRENT_TIME, so let's use those (your database may have other functions) --
select theDate, theTime from theTable where theDate = ( select max(theDate) from theTable where theDate <= current_date ) and theTime = ( select max(theTime) from theTable where theDate = ( select max(theDate) from theTable where theDate <= current_date ) and theTime <= current_time )
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.