SQL replace date values if date is Saturday or Sunday

Question

(Sorry, I couldn't think of a better title)

I have created the following select statement (SQL Server) to work as a view.

SELECT DISTINCT 
       dbo.ECB.Currency + '-' + 
           CAST(dbo.ECB.Date AS varchar(30)) AS ComboDate, 
       dbo.ECB.Rate AS ECBrate 
FROM 
    dbo.ECB 
INNER JOIN
    dbo.MyTable ON dbo.ECB.Date = dbo.MyTable.CutoffDate 
                AND dbo.ECB.Currency = dbo.MyTable.MyCurrency

My problem:

What I've noticed is that I have Saturday and Sunday dates in my CutoffDate column but not in the ECB date column - meaning I have no rates for those days.

Since I use the CutoffDate in Excel calculations I would like the view to "replace" the date values for Saturday and Sunday with Friday.

I found this working snippet and modified it to my needs.

SELECT 
    DISTINCT
    CASE WHEN DATEPART(weekday, dbo.CutoffDate) IN (6, 7) 
         THEN DATEADD(d, -((DATEPART(weekday, dbo.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.CutoffDate) 
         ELSE dbo.CutoffDate
    END
FROM
    MyTable

Now I need to find a way to combine both structures and this is where I'm hoping that you can help me out with.

I wanted to add the second select statement to my view but failed in doing so.

thank you.

It looks like you've got an approach that's trying to be clever to adjust the date regardless of the `DATEFIRST` setting for the session - but then you only invoke it if the day number is 6 or 7, which totally depends on the `DATEFIRST` setting. Simplicity would suggest you code for a single `DATEFIRST` setting if that's all you need and you're comfortable with knowing it's not a robust solution. Robustness would suggest making the entire expression able to deal with any `DATEFIRST` setting. — Damien_The_Unbeliever, Feb 20 '13 at 14:28

score 2 · Accepted Answer · answered Feb 20 '13 at 14:14

2

If you move your case statement into the join, it should work.

Change

dbo.MyTable ON dbo.ECB.Date = dbo.MyTable.CutoffDate

To

dbo.Mytable ON dbo.ECB.Date = case when datepart(weekday, dbo.CutoffDate) IN (6,7) 
    then dateadd(d, -((datepart(weekday, dbo.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.CutoffDate) 
    else dbo.CutoffDate 
end

Hope this helps.

answered Feb 20 '13 at 14:14

Kyle

4,421
22
32

Not a problem. Glad I was able to help. You can put a lot of things in joins, `case`, `isnull`, `cast`, and many others. – Kyle Feb 20 '13 at 14:35
I now realized that I need the ECB.Date to use the CutoffDate if the CutoffDate is Sat or Sunday. For example: If CutoffDate = 2012-02-17 (Sunday) then set the ECB.Date to 2012-02-17 as well but use the ECB.Rate of the ECB.Date of the previous Friday. Otherwise my ComboDate string is set to USD-2013-02-15 but in my Excel the calculation with the CutoffDate is set to USD-2013-02-17 for which I then don't have a ECB.Rate again. – SvenB Feb 21 '13 at 07:14
@SvenB I'm not sure I understand what you are saying. Are you still trying to join using the same join statement, but change your select statement? It sounds like what you are wanting to do is move that `case` statement up into your select, only using `ECB.Date` instead of `CutoffDate`. – Kyle Feb 21 '13 at 15:05
sorry for the delay - I had other things to deal with - of course you were right with your last comment. I changed that now and it works perfectly. – SvenB Mar 06 '13 at 08:19

SQL replace date values if date is Saturday or Sunday

1 Answers1

Linked