Time frame query

[krotha]

I have a table called local_time which has two int fields hour and min. I need to get records between hour and time.

Here is my query.
select convert(nvarchar(10),local_time.Hour) + ':'+
convert(nvarchar(10),Local_time.Minute)
from local_time
where convert(nvarchar(20),local_time.Hour) + ':'+
convert(nvarchar(20),Local_time.Minute) between '6:00' and '10:45'

If I pass 6:00 to 9:45 works fine,
10:00 to 11:45 works fine
but when i pass 6:00 to 10:00 I don't get any records, even though there are records.

Thanks for any help

 

[SQLSister]

YOur problem is structural. Store times in a date time

As it is, the reason you get no records has to do with the fact that what you have is text not numbers, therefore the records it is looking for are not the ones you want. In text sorting 10:00 would be before 6:00. Therefore a record of say 6:45 would not be returned as being beween the two items.

If you can't change your structure to the correct one for this type of problem, then I suggest you convert your hours to minutes and add them to the minutes field. Then you have the exact minute of the day to use for a numeric type comparison.

Questions about posting. See faq183-874

 

[krotha]

Thanks SQLSister,
Since I can't change my structure, I used your suggestion converting hours into minutes and comparing the minutes. Works a like charm. Thanks for your help.

 

[krotha]

There is one more problem I see here.
If I add another date condition to this query, I get only for starting date records.


select convert(nvarchar(10),local_time.Hour) + ':'+
convert(nvarchar(10),Local_time.Minute)
from local_time
where local_time.date between '08/01/2004' and '08/30/2004'
and convert(nvarchar(20),local_time.Hour)*60 + convert(nvarchar(20),Local_time.Minute) between '6:00' and '10:45'

 

[SQLSister]

what is the data type of your date field?

Also I would convert my between clause on the times to minutes as well so you are comparing apples and apples.

Questions about posting. See faq183-874

 

[krotha]

Date data type is datetime.
I converted the times to munutes in between cluase also. Still having problem.

 

[SQLSister]

How about some data samples. Show what is inthe tables, what result you are getting and what result you want.

Questions about posting. See faq183-874

 

[krotha]

Local_time table
Date hour minute
08/01/2004 6 30
08/01/2004 6 45
08/01/2004 7 15
08/01/2004 7 30
08/24/2004 10 0
08/30/2004 6 45
08/30/2004 23 45

When I ran the query with date between 08/01/2004 and 08/30/2004 I am getting 4 records for 08/01/2004 instead of 7 records for entire dates.

 

[SQLSister]

Well the last record will not meet your time criteria as it is later than 10:45.

I'll have to cogitate on why it didn't return 6 records though.

Do you want to return all the records from a certain date and time to a certain date and time? Or the records for a range of dates that fall within the times on those dates?

For instance inthe first case a date/time of 08/01/2004 12:30
would be included. In the second scenario it would not.


Questions about posting. See faq183-874

 

[krotha]

I want to return all the records from a certain date and time to a certain date and time.

 

[SQLSister]

Then you need one between that combines date and time. And because the datetimes are not properly stored all in one date time field, you will need to convert all your information to varchar and concatentate the fields together and then convert it to date time.

cast(Convert(varchar (8),datefield, 101) +  ' ' + Hourfield + 
':' + minutefield as datetime)

Questions about posting. See faq183-874

 

[vongrunt]

How about good ol' DATEADD()?

 

[SQLSister]

Took me awhile to work it out but yeah dataadd will do it too.

select DATEADD (mi, hourfield*60+ minutefield, datefield) from table1

You know you get a much better answer from dateadd when you use mi for minutes instead of mm. Took me the longest time to figure out whty my answers were so odd.

Questions about posting. See faq183-874

 

[ESquared]

You know you get a much better answer from dateadd when you use mi for minutes instead of mm. Took me the longest time to figure out whty my answers were so odd.

Ha

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[krotha]

Thanks SQLSister taking so much time for me.
I am still struggling with this query. I having trouble creating between clause.So far not getting good results. Can you please give me example how shall I put this query in 'between' clause.

Thanks for your help again

 

[SQLSister]

Try:

select (insert fields here)
from local_time
where DATEADD (mi, [hour]*60+ [minute], [date]) > '08/01/2004' and <'09/01/2004'

Of course, substitute whatever your real field names are.

Questions about posting. See faq183-874

 

[krotha]

Thanks a lot for your help. That works.