Compare Time values of a datetime field

I have a table called 'JobSchedules' it contains datetime values for when to
start a job and when to end a job. The date part of the equation is
irrelevant as the schedule is meant to start and stop jobs daily.
How can I do a query to return jobs with a starttime = 9:30 a.m. (for
example) or where the time portion of getdate() is between the StartTime and
EndTime (time portions).
There has to be relatively simple method to do this right?You should make sure the DATE portion is always the same value.
'20050101' for example is Jan 01 of 2005. Then the only relevant part will
be the time and you can use = or Between and compare directly.
Andrew J. Kelly SQL MVP
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OdXxi9zRFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
>|||>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
Then why not always use the same date (e.g. 1900-01-01)?

> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
If the dates are all different, then with your existing data you will have
to do messy converts etc. One way would be:
SELECT columns FROM table WHERE CONVERT(CHAR(8), column, 108) = '09:30:00'
Not very efficient. I would recommend doing this:
UPDATE table SET column = CONVERT(CHAR(8), column, 108)
And updating the application(s) and/or stored procedure(s) to insert time
only. Then the dates will all be 1900-01-01. Now you can use an index, and
say:
DECLARE @.startTime SMALLDATETIME
SET @.startTime = '9:30'
SELECT columns FROM table WHERE starttime = @.startTime
(If you need date only for some other reason, e.g. indicating when the
recurring schedule started, then use a separate column.)|||Try,
...
where
getdate() between cast(convert(varchar(11), getdate(), 126) +
right(convert(varchar(50), StartTime, 126), 12) as datetime)
and cast(convert(varchar(11), getdate(), 126) + right(convert(varchar(50),
EndTime, 126), 12) as datetime);
No search-arguments in the expression, so do not expect and index s.
AMB
"Roger Twomey" wrote:

> I have a table called 'JobSchedules' it contains datetime values for when
to
> start a job and when to end a job. The date part of the equation is
> irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime a
nd
> EndTime (time portions).
> There has to be relatively simple method to do this right?
>
>