Post reply

sql to bypass "dayofweek" and "days" functions

  • December 20, 2017 at 6:28 pm

    #409881

    This one's a bit more challenging!

    We are transitioning to sql server from a different type of system which made use of "days" and "dayofweek" functions which transform dates to unique serial numbers and extracts the day of week and turns it into a number so that we can use dates for calculations. But sql server apparently doesn't have these functions available. Does anyone know another way to reconstruct this calculation?

    CASE
    WHEN DAYOFWEEK(convert(date,kp_aspirationalduedate)) = 7 THEN DAYS(convert(date,kp_aspirationalduedate)) - DAYS(CURRENT DATE) - 1
    WHEN DAYOFWEEK(convert(date,kp_aspirationalduedate)) = 1 THEN DAYS(convert(date,kp_aspirationalduedate)) - DAYS(CURRENT DATE) - 2
    ELSE DAYS(convert(date,kp_aspirationalduedate)) - DAYS(CURRENT DATE) END AS "DAYS"

    Thanks!

  • December 20, 2017 at 6:38 pm

    #1973029

    SELECT

    CASE

    WHEN DatePart(dw,(Convert(date, kp_aspirationalschedule)))=7 THEN DateDiff(dd, GetDate(), kp_aspirationalschedule)-1

    WHEN DatePart(dw,(Convert(date, kp_aspirationalschedule)))=1 THEN DateDiff(dd, GetDate(), kp_aspirationalschedule)-2

    ELSE DateDiff(dd, GetDate(),Convert(date, kp_aspirationalschedule))

    END AS [DAYS]

  • December 21, 2017 at 9:51 am

    #1973118

    DayOfWeek 7 is ambiguous in SQL Server, since SQL allows the week to start on any day that you specify.

    Instead, I'd strongly urge you to use known day values.  SQL's base date is day 0 / 1900-01-01, a Monday.  Therefore, a days displacement from that date can be used to determine day of week while ignoring current SQL date settings.

    My best guess is that your code is adjusting Saturday back one day and Sunday back two days, if not, adjust the code below as needed.  0=Mon,6=Sun.  The CTE is just for initial use; after a while, you won't need it.


    ;WITH days AS
    (
      SELECT 5 AS Saturday, 6 AS Sunday
    )
    ...
    CASE
    WHEN DATEDIFF(DAY, '19000101', convert(date,kp_aspirationalduedate)) % 7 = Saturday THEN ...
    WHEN DATEDIFF(DAY, '19000101', convert(date,kp_aspirationalduedate)) % 7 = Sunday THEN ...
    ELSE ... END AS "DAYS"

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 3 posts - 1 through 2 (of 2 total)

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