Results 1 to 10 of 10

Thread: Using DateDiff

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Using DateDiff

    In my SQL I'm using

    DATEDIFF(WEEK, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [Weeks Waiting]

    which works great but the problem is that its rounding things in a normal way, but what I need to do is actually round it down, ie
    where a Weeks Waiting shows on the query as 13 but the actual answer is 12.86 I need the query to show as 12

    This is my SQL
    Code:
    SELECT    [Waiting List Identifier]
    , [Waiting List Name], [Date on Waiting List],
    				DATEDIFF(WEEK, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [Weeks Waiting]
    FROM         jez.REP_OP_WAITING_LIST
    GROUP BY [Waiting List Identifier]
    , [Waiting List Name], [Date on Waiting List], [Date Removed from Waiting List]
    HAVING      ([Waiting List Name] = 'pdd') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pp') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pga') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pbcs') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pbcn') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pd') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'pdos') AND ([Date Removed from Waiting List] IS NULL)
    example Date on Waiting List = 2008-07-03 00:00:00.000
    Current SQL says Weeks Waiting = 13

    If I did what wanted in Excel it would show as this ROUNDDOWN((N$1-F2)/7,0) and then show me the result as 12
    or (N$1-F2)/7 = 12.86

    How can I do this to get me the rounded down number?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Get your datediff in days and divide by 7, using FLOOR() to round down.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    How do you mean?

    I've been googleing FLOOR() but it doesnt seem to make much sense to me, could you explain.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    I've been googleing FLOOR() but it doesnt seem to make much sense to me
    Rly? http://msdn.microsoft.com/en-us/library/ms178531.aspx
    Code:
    SELECT    FLOOR(12.86)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I understand that and tested it to see how it works and thats what I'm looking for, the thing I didnt understand was how I can build that into the SQL in thread1

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In your excel code, you divide by 7 to get the number of weeks.
    In T-SQL you are using the WEEKS aregument for datediff. This means the number is returned rounded - you can't unround a rounded number. If you change this to DAYS and divide by 7 you can then round the result yourself.

    BTW - all the stuff in your having clause should be in the where clause instead e.g.:
    Code:
    WHERE [Waiting List Name] IN('pdd', 'pp', 'pga'/*and so on....*/) AND [Date Removed from Waiting List] IS NULL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I'm saying rounding but that is not strictly correct:
    Quote Originally Posted by bol
    The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result specified by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2007
    Posts
    183

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Jez - StalyVegas: that's in my neck of the woods dontcha know.

    Do us a favour Jez - if you post questions in multiple forums please could you link between them? It can be a bit galling to find you've duplicated work done somewhere else already or gone up a blind alley unnecessarily. I know these questions are not exactly the same but there is some cross over, possibly even more as they evolve

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Pootle, a fellow StalyVegasan then :-) excellent

    No problem I'll link the threads next time...

Posting Permissions

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