Filtering on parts of a date/time field

[matturbanowski]

Hi,
I have a date/time field in a SQL2000 database, and what I would like
to do is to filter on a specific part of the field, for example the
time or hour.

Supposing I have a set of data for the last 5 years and would like to
filter out any records which are outside working hours i.e. I would
like to show records where the time is between 9am and 5pm.

Does anyone know if there is a simple way to do this in SQL? If you
simply don't specify the date part in the where clause (e.g. WHERE
issuedatetime between '09:00:00' and '17:00:00') it defaults it to
1900-01-01 so basically no data is returned.
The only way I can see to do this is by using the DATEPART function,
converting it to a varchar, appending 1900-01-01 on to it and
converting it to a datetime, and then using the where clause as stated
above. This is quite a long-winded way, however. Any other suggestions?

Thanks,
Matt

[SQL Menace]

select * from TableName where datepart(hh,ColumnName) between 9 and 17

Denis the SQL Menace
http://sqlservercode.blogspot.com/

matturbanowski wrote:

Hi,
I have a date/time field in a SQL2000 database, and what I would like
to do is to filter on a specific part of the field, for example the
time or hour.

Supposing I have a set of data for the last 5 years and would like to
filter out any records which are outside working hours i.e. I would
like to show records where the time is between 9am and 5pm.

Does anyone know if there is a simple way to do this in SQL? If you
simply don't specify the date part in the where clause (e.g. WHERE
issuedatetime between '09:00:00' and '17:00:00') it defaults it to
1900-01-01 so basically no data is returned.
The only way I can see to do this is by using the DATEPART function,
converting it to a varchar, appending 1900-01-01 on to it and
converting it to a datetime, and then using the where clause as stated
above. This is quite a long-winded way, however. Any other suggestions?

Thanks,
Matt

[Roy Harvey]

Look at the datepart function:

SELECT DATEPART(year,getdate()),
DATEPART(quarter,getdate()),
DATEPART(month,getdate()),
DATEPART(week,getdate()),
DATEPART(weekday,getdate()),
DATEPART(day,getdate()),
DATEPART(dayofyear,getdate()),
DATEPART(hour,getdate()),
DATEPART(minute,getdate()),
DATEPART(second,getdate()),
DATEPART(millisecond,getdate())

It is also worth becoming familiar with DATEADD() and DATENAME().

Roy Harvey
Beacon Falls, CT

On 3 Jan 2007 10:02:48 -0800, "matturbanowski"
<sa***@matturbanowski.co.ukwrote:

>Hi,
I have a date/time field in a SQL2000 database, and what I would like
to do is to filter on a specific part of the field, for example the
time or hour.

Supposing I have a set of data for the last 5 years and would like to
filter out any records which are outside working hours i.e. I would
like to show records where the time is between 9am and 5pm.

Does anyone know if there is a simple way to do this in SQL? If you
simply don't specify the date part in the where clause (e.g. WHERE
issuedatetime between '09:00:00' and '17:00:00') it defaults it to
1900-01-01 so basically no data is returned.
The only way I can see to do this is by using the DATEPART function,
converting it to a varchar, appending 1900-01-01 on to it and
converting it to a datetime, and then using the where clause as stated
above. This is quite a long-winded way, however. Any other suggestions?

Thanks,
Matt

[matturbanowski]

OK, Thanks for your replies.