How do I calculate the time between the current time in the database? Suppose the date in the database is:
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 )
This was first published in July 2001