How can I select records between start and end time

[hoggle]

I have two fields startTime and EndTime both are datetime format and hold the current date as well as a user defined time. ie: startTime(7/1/2003 11:34:00 AM) and endTime(7/1/2003 3:34:00 PM)

I only need these fields to get the time from not the actual date.

How can I do a between on them and just have it pull all records (regardless of date, but inbetween the two times)

this is what I have to do the hours with (but I need minutes as well)

(from my vb code)
note: Hour(Now) just pulls the current hour

"select * from tblShifts SSID WHERE " & Hour(Now) & " BETWEEN datepart(Hh, SSID.StartTime) AND datepart(Hh, SSID.EndTime) "


thanks for any help

 

[SQLBill]

You don't need to use DATEPART. Try this:

select right(convert(varchar,SSID.StartTime,0),7)

-SQLBill

 

[roccorocks]

Try this Hoggle:

SELECT *
FROM yourTable
WHERE (RIGHT(CONVERT(varchar, yourColumn, 0), 7) BETWEEN '11:58:56 AM' AND '8:15:30 PM')

The only Problem with this is the times are hard coded and it does not take into account the "DATE". So if you have 2 months worth of data and you do this based on the Time, you will pull 2 months worth of Data between those time frames.

you may have to add something like this to the where clause:

where (Left(CONVERT(varchar, dateNew, 0), 10) = or between date ranges.

SQLBill had the right idea (he almost always does!)

Rocco

 

[hoggle]

I think that will work, do you know if I pass in something like 13:45 if sql server will convert that for me into 1:45 PM so it can do the BETWEEN select?

I need to use military time in my program because that is how everything is being done currently.

 

[hoggle]

both will work because I don't want the date to be a factor against the results.

 

[roccorocks]

Here you go:

SELECT *
FROM yourTable
WHERE RIGHT(CONVERT(varchar, dateNew, 14), 12)BETWEEN '00:00' AND '24:00')

You might want to research the Conversion Codes (refered to as STYLE, the number 14 represents this STYLE area) for the CONVERT function, just for future reference.

Just modify the times.

Rocco

 

[roccorocks]

Dang typos, here you go:

SELECT *
FROM yourTable
WHERE RIGHT(CONVERT(varchar, dateNew, 14), 12) BETWEEN '00:00' AND '24:00'

Rocco

 

[SQLBill]

Try this:

"select * from tblShifts SSID WHERE " & Hour(Now) & " BETWEEN right(convert(varchar,SSID.StartTime,0),7) AND right(convert(varchar,SSID.EndTime,0),7) "

Before, I only gave you part of the script to show what you needed to do. But that's the whole script that I believe you are looking for.

-SQLBill

 

[hoggle]

Since we use julian date and military time, I just decided to hold the date as char fields in the database.
Now I can easily go through them...
thanks everyone for the ideas, all of them worked one way or another...but I feel in the long run this will be alot easier (because we display data in military time and julian date...to much conversion if I go with sql datetime types)

thanks again