Filtering by time after date

Question

Post reply

Filtering by time after date

Karl Patterson

Newbie

Points: 7
More actions
May 11, 2004 at 8:46 am

#85332

I want to filter a result set by time after filtering by date.
Basically I want to find out all transactions that happened between say 9am and 10am with a period of a week,
eg:
select * from transactions where tran_date between '03/01/2004' and '03/05/2004'
to return the transactions for the first week of May.
I then want to filter that result set into periods i.e. 9am-10am (Morning), 12-2pm (Lunch), 5pm-7pm (Dinner), giving me all transactions in a week for a certain period.
I am using Delphi and ADO and can parse it up in delphi but i was wondering if there was a neat way to do it with SQL.
Cheers.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 1

One way:

select

case

when datepart(hour,tran_date) between 9 and 9 then '9am-10am (Morning)'

when datepart(hour,tran_date) between 12 and 13 then '12-2pm (Lunch)'

when datepart(hour,tran_date) between 17 and 18 then '5pm-7pm (Dinner)'

else 'Not morning, lunch, dinner' end,

*

from

transactions

where

tran_date between '03/01/2004' and '03/05/2004'

/rockmoose

You must unlearn what You have learnt

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Well, that really depends on how you want to handle it and what should be the final result... here one possibility:

select * , CONVERT(varchar(5), tran_date,14) AS tran_time

from transactions

where tran_date between '03/01/2004' and '03/05/2004'

This will give you a new column with e.g. '11:35', and you can then use it to group your records in the application. If you use varchar(2), convert will return only hour without minutes and then you can GROUP BY and COUNT right in the SQL, using this column. Of course, it is also possible to use the CONVERT expression in the WHERE clause - whichever suits your needs better.

HTH, Vladan

Karl Patterson Newbie Points: 7 More actions · Answer 3

Cheers folks for the responses.

I found another quite neat way to do it where I can specify the times down to seconds.

select * from transactions where tran_date between '03/01/2004' and '03/05/2004' and convert(varchar(8), trandate, 108) BETWEEN '09:00:00' AND '09:59:59'

Karl.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

If you're looking to group them by hour (as it seems based on convert(varchar(8), trandate, 108) BETWEEN '09:00:00' AND '09:59:59' ), why not this:

Select * from transactions where tran_date between '03/01/2004' and '03/05/2004' and datepart(hh,trandate)=9

Or if you want to do grouping by hour:

Select *, datepart(hh,trandate) AS datehour from transactions where tran_date between '03/01/2004' and '03/05/2004' Group by datepart(hh,trandate)

Karl Patterson Newbie Points: 7 More actions · Answer 5

The time periods can and will change so I was needing to group down to minute ( and even second ) if required.

Cheers folks!