Datetime question

Hello - easy question hopefully,

I am writing a query that will run every morning to get the calls from the previous 24 hours. It is however broken up in core and non core hours (so it will actually be 2 queries), core being 6.30 am till 9.30 pm the previous day and non core being 9.30 pm till 6.30 am the next day. It looks at the CallStartDate field which is datetime field such as '04/11/07 7:00:00 AM'.

What will my WHERE clause look like? Do I have to split the time from the date in order to make this happen or is there a much easier way?

Thanks,

MB

This should do what you need it to:

create table YourTable
(
CallStartDate datetime
)
insert into YourTable
select '20070411 5:30'
union all
select '20070411 7:30'
union all
select '20070411 9:30'

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, CallStartDate)),
convert(datetime,convert(varchar(10), CallStartDate,108)) ,
CallStartDate
from YourTable
--past day
where CallStartDate >= dateadd(day,-1,getdate())
--not (between 6:30 and 9:30, not including 9:30_
and convert(datetime,convert(varchar(10), CallStartDate,108)) >= '6:30'
and convert(datetime,convert(varchar(10), CallStartDate,108)) < '9:30'

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, CallStartDate)),
convert(datetime,convert(varchar(10), CallStartDate,108)) ,
CallStartDate
from YourTable
--past day
where CallStartDate >= dateadd(day,-1,getdate())
--not (between 6:30 and 9:30, not including 9:30_
and not(convert(datetime,convert(varchar(10), CallStartDate,108)) >= '6:30'
and convert(datetime,convert(varchar(10), CallStartDate,108)) < '9:30' )|||

Code Snippet

WHERE CallStartDate >

DATEADD(day, -1, CONVERT(DATETIME, CONVERT(char(11), GETDATE()) + ' 06:30AM')

AND CallStartDate <=

DATEADD(day, -1, CONVERT(DATETIME, CONVERT(char(11), GETDATE()) + ' 09:30PM')

|||

Something like this could work for you:

Code Snippet

--core
WHERE ( CallStartDate >= dateadd ( day, -1, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
AND CallStartDate < dateadd ( hour, -12, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
)

--non core
WHERE ( CallStartDate >= dateadd ( hour, -12, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
AND CallStartDate < ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' )
)

|||it's working!! Thanks a million guys