Q

Latest date/time before current date/time

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close