asked on

MS SQL Between Hours/Date

Hi,

I would like to run a query that gives me the data from March 1st to March 20th but only between the hour of 11:00 pm to 12:30 AM

table: tbl1
field: fieldAB which is datetime ie 2011-01-07 10:03:27.000 2011-01-07 00:23:20.000

so something like this

select * from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and fieldAB '11:00' and '00:30'

Thanks

SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

try something like this...

select convert(varchar,fieldab,110) yourdate
from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,fieldAB), 0)), dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,fieldAB ), 0))
group by convert(varchar,fieldab,110), col1

SOLUTION

ambidextrous

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

Sorry, I missed the between

select convert(varchar,fieldab,110) yourdate
from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and fieldab between dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,fieldAB), 0)) and dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,fieldAB ), 0)) -- added between statement...
group by convert(varchar,fieldab,110), col1

ezzadin

ASKER

Thanks All.

Sventhan, I'm trying your solution but can't get any result. what do you mean by Between statement?

I actually moved all the data between March 1st and March 20 to a separate table. now I just need to run a query that gives me data only between 11 pm and 12:30 am

this doesnt return any result: please note that FieldAB is formatted as datetime

select * from tbl1
where fieldAB between dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,calldatetime), 0)) and dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,calldatetime ), 0))

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

The idea here is get the midnight of the particular day (fieldAB) and look back/forward -1hr/+30minutes...

This DATEADD(dd, DATEDIFF(dd,0,calldatetime), 0 will get you the midnight. 12:00 PM.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

@Sharath

Are you sure about this logic question (Question key is not working in my keyboard)

AND (CONVERT(VARCHAR,your_col,108) <= '00:30:00'
OR CONVERT(VARCHAR,your_col,108) >= '23:00:00')

I guess we talking about 2 different dates... If works I'm fine.

sventhan

@Sharath

Never mind.

ezzadin

ASKER

@Sharath

your solution worked on to me and I got the data that I was looking for.

@sventhan

I tried your query but never got any data back.. thanks for the help though