Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: 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??

  2. #2
    Join Date
    Jan 2004
    Posts
    6
    This should be a good start:
    Code:
    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)

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    One more try...

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

    Is it correct?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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)}
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    set datefirst 1
    declare @timestamp datetime
    set @timestamp = getdate()
    select (datepart(dd,@timestamp)-1)*24+datepart(hh,@timestamp)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time then one week has 167 and one has 169 each year. If you need to keep Sidereal 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

  7. #7
    Join Date
    Mar 2004
    Posts
    45
    declare @dt datetime
    set @dt = '20040309 1:00'
    select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@dt)+@@DATEFIRST-2)%7,LEFT(@dt,13)),@dt)

    Hans.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •