If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > DB2 > Calculate a Date in SQL Statement

 SDyke Registered User 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)?
 rdutton Registered User Join Date: Dec 2008 Posts: 76
 where dayofweek(ftdate) in (2,3,4,5,6) __________________ RD
 SDyke Registered User 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.
 spartiatis Registered User 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? __________________ http://www.sqltipsandtricks.com
 rdutton Registered User 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
 SDyke Registered User 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.
 SDyke Registered User 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"
 spartiatis Registered User 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" +; __________________ http://www.sqltipsandtricks.com
 stolze Registered User 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
 spartiatis Registered User Join Date: Aug 2009 Posts: 16
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 __________________ http://www.sqltipsandtricks.com

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Forum Rules