Check a time is between two times

[Naoise]

I have 2 datetime fields (can change to varchars if necessary) and basically want to find out if the current time is between those two times. Dates do not matter, it is just the time I am looking for. The times are stored in 24hr format so using datepart on the hour is not going to be of much use. Any ideas?

 

[rac2]

You might get a better answer by posting in a forum for the particular brand of database you are using.

In MS SQL, a column of datatype DATETIME contains numerical values counting milliseconds since January 1, 1753. This can be displayed according to various formats, but most have a date and a time part. The display is always a string.

In Access, I believe there may be datatypes for TIME, DATE, and DATETIME.

Are those fields from a form, or are they columns in a table?

Are the times actually strings already? Since you say they are in 24hr format?

The integer returned by the DATEPART(hour, datetime_value) is a number between 0 and 23. Do you mean that 8am should be treated the same as 8pm?

 

[Naoise]

I am using MS SQL 2K.

This is my table

tableA
id| starttime| endtime

and starttime and endtime are of type datetime at the moment but can be of type string if a solution needs it to be so.

Datepart is of no use as if the time is 20:00 and starttime is 16:00 and endtime is 00:00 ie 12 midnight then datepart will say that 20 is not between 16 and 0 even though in terms of time it is.

If I have the fields as datetimes then it will be comparing their dates if I use datediff or something. So how do you decide if a time is between two times either as strings or as extracted values from a datetime field?

Thanks for replies,
Naoise

 

[rac2]

Hmmm. How to say this.

If, as you say startime is a DATETIME datatype then the value of startime is never 16:00; it must be 16:00 on some date.

Likewise endtime must be 00:00 on some date.

If the value of starttime is June 13 2005 16:00:00.007 and the value of endtime is June 12 2005 00:00:00.777, and the target value we wish to check is merely 8pm, then the target value is not between the starttime and the endtime.

If the value of starttime is June 13 2005 16:00:00.007 and the value of endtime is June 14 2005 00:00:00.777, then the target value is between the starttime and endtime.

So build a string representing a valid DATETIME value from getdate() and the target value. Convert the string to a DATETIME value. (MS SQL might do that automatically for us, but I like to be explicit.) Then compare that constructed DATETIME value to starttime and endtime in the usual way.

/*Get the date piece of the current date and time.*/
DECLARE @strDateToday VARCHAR(16)
SET @strDateToday = CONVERT( CHAR(10), getdate(), 101)

/* Build a string representing a valid date. */
SET @strDateToday =  @strDateToday + ' 20:00'

/* Show the string and the DATETIME value.*/
SELECT @strDateToday, CAST( @strDateToday AS DATETIME )

 

[Naoise]

Sorry, my apologies, I have confused the issue.

table A
id | starttime | endtime
1 18:00 09:00


where starttime and endtime are strings.

These strings are in 24hr format, 00:00, 01:00 etc

How is it possible to find out if the current time pulled via getdate() is in between the starttime and endtime strings?


In the example above if I cast the strings as datetimes then we will be comparing all times on this day "1900-01-01" and so if the current time is 19:00 = "1900-01-01 19:00:00.000", it will not be between "1900-01-01 18:00:00.000" and "1900-01-01 09:00:00.000" but for my purposes 19:00 is between 18:00 and 09:00. Using datepart on the strings will give me a SQL QUERY that is trying to check if 19 is between 18 and 9 which it is not. Have I explained it a bit better or made a hash of it again?

 

[PHV]

Use a case when starttime <= endtime ... else ... construct.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

I don't follow, I cannot compare two strings using the '<' operator and if I convert the strings into datetimes then I will have the problems I mentioned in my last post.

Thanks for replies

 

[PHV]

I cannot compare two strings using the '<' operator
Really ?

 

[Naoise]

Will two strings be compared correctly using operators like '>' in terms of time? I mean will '20:00' be seen as > '18:00' and < '09:00'? I thought that would not work? I'm open to correction

 

[Golom]

Yes. Your examples will compare correctly. "<" or ">" applied to text strings just determines "Before" or "After" in the collating sequence. Note however that

"10:00 AM" < "09:00 PM" (for example)

will NOT compare correctly. You must use military (i.e. 24 hour clock) times with two-digit hours to get correct comparisons.

 

[PHV]

And, the intent of the case construct is to add 24 hours to endtime when endtime < starttime ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Sorry for following up again but I cannot get this to work.


tableA
id | starttime | endtime
1 18:00 09:00

where starttime and endtime are strings.

SELECT id from tableA where '21:00' > starttime and '21:00' < endtime

This returns no results.

SELECT id from tableA where '21:00' between starttime and endtime

This also returns no results

In both cases the first part of the clause succeeds ie '21:00' vs '18:00' but fails on '21:00' vs '09:00' because 21 is not less than 9 or between 18 and 9. Have I missed something or how were the last 2 solutions to be implemented.

Thanks for replies.
Naoise

 

[PHV]

I don't see any WHEN endtime < starttime in your where clause.
Have you understood my suggestion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

select * from tableA where '21:00' < starttime and '21:00' > endtime when endtime < starttime ???


I get incorrect syntax near keyword 'when' after running that sql

I probably have misunderstood, could you explain it if you have the time, thanks.

By the way startime and endtime can be anytime on the 24 hour clock.

Thanks,
Naoise

 

[PHV]

In your RDBMS manual pay attention to CASE WHEN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

This plain does not work and I don't see how it could.

MSSQL 2K

select id,
(select case when endtime < starttime then 'true' else 'false' end
from tableA
where '21:00' > starttime
and '21:00' < endtime)
from tableA

Could you show me the sql you have in mind?

Thanks,
Naoise

 

[PHV]

Finally, why not simply this ?
WHERE starttime <= '21:00'
AND (endtime < starttime OR endtime >= '21:00')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886