# Thread: Calculate a Date in SQL Statement

1. Registered User
Join Date
Aug 2009
Posts
16

## Unanswered: 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. Registered User
Join Date
Dec 2008
Posts
76
where dayofweek(ftdate) in (2,3,4,5,6)

3. Registered User
Join Date
Aug 2009
Posts
16
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. 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

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. 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.

6. Registered User
Join Date
Aug 2009
Posts
16
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

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. Registered User
Join Date
Aug 2009
Posts
16
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. 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" +;

9. 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```

10. Registered User
Join Date
Aug 2009
Posts
16

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.