How to return current hour of week?

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:

DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME

--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)

This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.

The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...

(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}

Is it correct?|||You need another set of parenthesis for proper order of operations:

declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.

These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.

-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)

Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.

how to restrict SQL running thread

Hi,
We have a big report running on SQLRS2000+SP2. The report has multiple
datasets and queries. When report starts running, the database server it
connects to run query and pull data will start 33 threads and immediately
reach 100% CPU usage for all 8 CPUs. Databases is over 1TB and sets on EMC
SAN. There is no blocking during report generation. There is no sub-report,
so I assume there is only one DB connection.
We are wondering is it possible to restrict the number of threads
instantiated or is it possible to serialize the report generation, only run
1 thread at a time. Please help.
Jasonyou can set the maxdop option in your queries to limit the number of CPU
used during the execution of the query.
select ... from ... option (maxdop 1)
this reduce the number of CPU used in a parrallel query
the maxdop option can be setup at the server level instead-of the query
level; but not recommanded until you suffer performance issues
test the query, reducing the maxdop can reduce the query performance.
if the query takes 1min instead-of 10sec. the user will not be happy!
why its a problem for you if the queries use all the CPU?
"Jason Wang" <aa@.aa.com> wrote in message
news:eSLkGBeRGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a big report running on SQLRS2000+SP2. The report has multiple
> datasets and queries. When report starts running, the database server it
> connects to run query and pull data will start 33 threads and immediately
> reach 100% CPU usage for all 8 CPUs. Databases is over 1TB and sets on EMC
> SAN. There is no blocking during report generation. There is no
> sub-report, so I assume there is only one DB connection.
> We are wondering is it possible to restrict the number of threads
> instantiated or is it possible to serialize the report generation, only
> run 1 thread at a time. Please help.
> Jason
>