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)?

where dayofweek(ftdate) in (2,3,4,5,6)

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.

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?

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.

This works great, thanks.

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"

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" +;

And you can also do this:

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