Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2009
    Posts
    14

    Calculate a Date in SQL Statement

    I have SQL that will get dates from data file

    Select recseq, DATE(ftdate - 7 days) As d1

    Is there a way to get this date calculation to only look at working days(Monday - Friday)?

  2. #2
    Join Date
    Dec 2008
    Posts
    76
    where dayofweek(ftdate) in (2,3,4,5,6)
    RD

  3. #3
    Join Date
    Aug 2009
    Posts
    14
    Can you show me the SQL string because I am not sure how to do this. It appears to me the where clause would only pull records where the ftdate is not on a wekkend date. I need the calculation of DATE(ftdate - 7 days) to skip weekend days if possible.

  4. #4
    Join Date
    Aug 2009
    Posts
    16
    Quick question for you. Will ftdate be always between Monday-Friday or weekend days as well?
    It sounds like you are really looking for something like this
    DATE(ftdate - 7 business days)

    I would probably use a CASE statement.
    CASE ftdate is Monday or Tuesday subtract 11 days (7 days + 4 weekend days)
    CASE ftdate is Wed - Friday subtract 9 days (7 days + 2 weekend days)

    I didn't test it out but you get the logic right?

  5. #5
    Join Date
    Dec 2008
    Posts
    76
    because you are calculating (ftdate - 7 days) it therefor follows that the day of the week of ftdate must be the same as (ftdate - 7 days). It further follows that if you don't pull weekend dates you won't calculate weekend dates.
    RD

  6. #6
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by spartiatis
    Quick question for you. Will ftdate be always between Monday-Friday or weekend days as well?
    It sounds like you are really looking for something like this
    DATE(ftdate - 7 business days)

    I would probably use a CASE statement.
    CASE ftdate is Monday or Tuesday subtract 11 days (7 days + 4 weekend days)
    CASE ftdate is Wed - Friday subtract 9 days (7 days + 2 weekend days)

    I didn't test it out but you get the logic right?
    This works great, thanks.

  7. #7
    Join Date
    Aug 2009
    Posts
    14
    One more thing though. Here is my SQL. Is there a way to calculate the difference between the two dates in a fourth column?

    testconnString = "Select recseq," +;
    " (Select CASE WHEN checkerd is NULL THEN DATE(current_date) ELSE checkerd END" +;
    " From webprddt6.drawmext3 Where recseq = dwgindex) As f2," +;
    " CASE WHEN dayofweek(ftdate) = 1 THEN DATE(ftdate - 10 days)" +;
    " WHEN dayofweek(ftdate) = 2 THEN DATE(ftdate - 11 days)" +;
    " WHEN dayofweek(ftdate) = 3 THEN DATE(ftdate - 11 days)" +;
    " WHEN dayofweek(ftdate) = 4 THEN DATE(ftdate - 9 days)" +;
    " WHEN dayofweek(ftdate) = 5 THEN DATE(ftdate - 9 days)" +;
    " WHEN dayofweek(ftdate) = 6 THEN DATE(ftdate - 9 days)" +;
    " WHEN dayofweek(ftdate) = 7 THEN DATE(ftdate - 9 days)" +;
    " END As ftdate" +;
    " From webprddt6.resmngta3d Where projindex = 710"

  8. #8
    Join Date
    Aug 2009
    Posts
    16
    You mean the different from ftdate to the "new" ftdate? That would be your 11 or 9 days right? Repeat the CASE statement and return just the days you are subtracting.
    ...
    " CASE WHEN dayofweek(ftdate) = 1 THEN 10" +;
    " WHEN dayofweek(ftdate) = 2 THEN 11" +;
    ... and so on.

    Just a quick tip on making your CASE statement simpler try using this:

    " CASE WHEN dayofweek(ftdate) = 1 THEN DATE(ftdate - 10 days)" +;
    " WHEN dayofweek(ftdate) = 2 OR dayofweek(ftdate) = 3 THEN DATE(ftdate - 11 days)" +;
    " ELSE DATE(ftdate - 9 days)" +;
    " END As ftdate" +;

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And you can also do this:
    Code:
    CASE dayofweek(ftdate)
       WHEN 1 THEN ftdate - 10 DAYS
       WHEN 2 THEN ..
       ...
    END
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Aug 2009
    Posts
    16

    Post Adding and Subtracting Business Days to a Date

    Keep in mind that this will work if you are adding 7 days only. If the number of days you are adding is different it may not work.
    I am actually working on a function that will be able to add/subtract any number of days. I am also working on a version that will allow you to exclude holidays as well.

    Check out my article here and let me know what you guys think.
    Adding and subtracting Business Days to a Date

Posting Permissions

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