Query with date and Time

Question

Viewing 4 posts - 1 through 4 (of 4 total)

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

You would select all the records with a datetime greater than 4 days ago first, then filter based upon the time.

Something like:

SELECT {columns}

FROM yourTable

WHERE datecolumn >= dateadd(day, datediff(day, 0, getdate()) - 4, 0) -- 4 days ago at midnight

AND datepart(hh, datecolumn) >= 9

AND datepart(hh, datecolumn) <= 13;

Not tested - if you want a tested solution, review the article I link to in my signature on how to post questions to get better answers faster.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

animo3d Newbie Points: 7 More actions · Answer 2

Thanks, worked very well

had a little trouble with the syntax

the correct syntax is

SELECT * FROM TableName WHERE datecolumn BETWEEN #15/04/2009# AND #22/04/2009# AND datepart('h',datecolumn )>=9 AND datepart('h',datecolumn )<=13

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

I see - you are actually doing this in Access and I gave you SQL Server T-SQL syntax. At least you were able to see what was happening and convert.

Glad you were able to get it to work.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll