Conditionally setting a variable in SQL

Question

I'm trying to do this:

If the Day parameter is the current day, then set @EndDate to be yesterday instead
If the Day parameter is in the future, set @EndDate to yesterday.

I've tried a few different approaches including two down below. I'm fairly new to programming so odds are I'm missing something fairly simple. Basically, I am trying to set @EndDate conditionally, depending on what @Day is set to be.

DECLARE @Day DATETIME

SET @Day = '09/2/17 12:50'  
SET @Day = DATEADD(dd, DATEDIFF(dd, 0, @Day), 0)

DECLARE @Enddate DATETIME

SET @Enddate = CASE @Day  
                  WHEN @Day < GETDATE() THEN GETDATE() - 1
               END

--SET @Enddate = @Day  
--     WHERE @Day < GETDATE()  
--SET @Enddate = GETDATE()-1    
--     WHERE@Day >= GETDATE()

Thanks

So just set @EndDate to be yesterday, since both conditions do the same. Did you mean to do that? — , Sep 04 '17 at 18:06
What about the time part? how do you what to handle it? day is today 11:00, now is 13:00 what time is yesterday? 11:00, 13:00 or 00:00 or 23:59? — Horaciux, Sep 04 '17 at 18:12
@Agapwlesu No, because if someone was to set Day to be any day before yesterday, I want Enddate to be that day. So, if Day = 4/5/15 then Enddate should equal 4/5/15 also. I only want Enddate to equal yesterday if Day is set to be the current day or a day in the future. — Maddie, Sep 04 '17 at 18:12
@Horaciux I already got rid of the time part when setting Day - — Maddie, Sep 04 '17 at 18:14
@Horaciux Oh, I see what you mean. Yeah, i need to get rid of time there as well. — Maddie, Sep 04 '17 at 18:18

score 2 · Accepted Answer · edited Sep 04 '17 at 20:19

You are mixing the two possible ways to write a case expression...

You can either use

CASE @day 
   WHEN GETDATE() THEN 'x' 
   WHEN DATEADD(DAY, 1, GETDATE() THEN 'y' 
END

or then you can use:

CASE 
   WHEN @day = GETDATE() THEN 'x' 
   WHEN @day > GETDATE() THEN 'y' 
END

This is the correct way:

SET @Enddate = CASE 
                  WHEN @Day < GETDATE() 
                     THEN DATEADD(DAY, -1, GETDATE())
                     ELSE GETDATE()
               END

Horaciux · Answer 2 · 2017-09-04T20:37:52.967

1

For clarification, variable @yesterday added as DATE without time

Declare @Day datetime

Set @Day = '09/02/17 12:50'
SET @Day = DATEADD(dd, DATEDIFF(dd, 0, @Day), 0)

Declare @Enddate Datetime

Declare @yesterday as date
SET @yesterday=dateadd(day,-1,getdate())

Set @Enddate = Case 
When @day<  @yesterday Then @day else @yesterday  End

Any values older than yesterday remains as is, other case sets yesterday

edited Sep 04 '17 at 20:37

answered Sep 04 '17 at 18:09

Horaciux

6,322
2
22
41

One should **NOT** use `GETDATE()-1` - that's the current date&time minus one ....... what!?!??!? Year? Month? Day? Minute? Hour? Second? Use a proper `DATEADD(....)` call instead to make it **crystal clear** what you're subtracting from the current date&time ... – marc_s Sep 04 '17 at 20:20
It is casted as DATE as OP required, no time involved, see previous comments in original question. @marc_s do you still think it should be modified? – Horaciux Sep 04 '17 at 20:26
Yes - it's still unclear - and even if you cast it to a `DATE` later on - it's still not obvious what "1" you've subtracted..... I would recommend to always use the `DATEADD()` approach to make your intent crystal clear to everyone who needs to read your code later on – marc_s Sep 04 '17 at 20:30
@Horaciux The other answer just ended up working better with what I was trying to do. – Maddie Sep 12 '17 at 18:01

Conditionally setting a variable in SQL

2 Answers2