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.

 
Go Back  dBforums > Database Server Software > DB2 > Calculate a Date in SQL Statement

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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)?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Posts: 76
where dayofweek(ftdate) in (2,3,4,5,6)
__________________
RD
Reply With Quote
  #3 (permalink)  
Old
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.
Reply With Quote
  #4 (permalink)  
Old
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
Reply With Quote
  #5 (permalink)  
Old
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
Reply With Quote
  #6 (permalink)  
Old
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.
Reply With Quote
  #7 (permalink)  
Old
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"
Reply With Quote
  #8 (permalink)  
Old
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
Reply With Quote
  #9 (permalink)  
Old
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
__________________

http://www.sqltipsandtricks.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are On