Post reply

days into completed weeks only

  • September 10, 2012 at 5:43 am

    #261300

    Hi

    how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal)

    Problem i have is I only want to count completed weeks:

    ie: 20 days is 2.86 weeks.

    need answer to be 2 and not 3 as i get in SQL.

    Thanks

  • September 10, 2012 at 7:14 am

    #1535665

    Is this what you need to do ?

    SELECT 2.86 /1 - 2.86 % 1

    SELECT 2.16 /1 - 2.16 % 1

    Result for both selects is 2.000000

    Note that the "%" is the modulo operator - check it out in BOL it is a powerful tool to become familiar with.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 10, 2012 at 7:36 am

    #1535681

    Thanks, sorted the issue now.

  • September 10, 2012 at 7:38 am

    #1535683

    i do: select FLOOR(2.86)

  • September 10, 2012 at 7:47 am

    #1535691

    LoosinMaMind (9/10/2012)

    Hi

    how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal)

    Problem i have is I only want to count completed weeks:

    ie: 20 days is 2.86 weeks.

    need answer to be 2 and not 3 as i get in SQL.

    Thanks

    watch your data types. using integers 20/7 = 2 since SQL Server throws away the remainder when performing integer math. if you want to be absolutely certain / more precise / more explicit, i would use float or decimal with FLOOR().

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • September 10, 2012 at 8:04 am

    #1535702

    LoosinMaMind (9/10/2012)

    Thanks, sorted the issue now.

    Proper forum etiquette would have you share your solution with others. It may help someone with a similar issue.

  • September 11, 2012 at 7:54 am

    #1536249

    Please check if this can help you!!!

    select CAST(20.0/7 AS INT) + CASE WHEN (20%7) > 3 THEN 1 ELSE 0 END

    Regards,
    Mitesh OSwal
    +918698619998

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply