Time between Date range

[digimortal]

Hi All,
I have table called Eventsl with four fields;
DTime: 01.07.2005 18:36:00
Name: Name
Surname: Surname
CardNo: 1234324

and I need to get all the records between 18:00:00 and 19:00:00 between the given date parameters.

Below is my query that does not work

SELECT DISTINCT CONVERT(varchar(8), DTime, 108) AS DTime2, DTime FROM EventsL
WHERE (Site = 'BAK AMBALAJ') AND (Door = 'TURNiKE GiRiS') AND (DTime > '"& Date1 &"') AND (DTime < '" & Date2 & "') AND DTime2 > '18:00:00' AND DTime2 < '19:00:00'

It gives the invalid coloumn error for DTime2???

Thnx in advance

 

[vongrunt]

You cannot reference column via alias (in this case DTime2). Either repeat entire expression (ugly, I know) or use derived table.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[digimortal]

My Bad...

SELECT DISTINCT CONVERT(varchar(8), DTime, 108) AS DTime2, DTime FROM EventsL
WHERE (Site = 'BAK AMBALAJ') AND (Door = 'TURNiKE GiRiS') AND (DTime BETWEEN '07.01.2005' AND '07.30.2005') AND (CONVERT(varchar(8), DTime, 108) BETWEEN '18:00:00' AND '19:00:00')

 

[vongrunt]

Original DTime column is of which data type - char, varchar, datetime, smalldatetime?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)