SQL- Special DATEDIFF condition

Question

I have a button which gets locked for previous month when we roll into new month.

Eg. if today's date is 04-06-2014, then the button will only be visible for this month only. For the previous month(May in this case), it will not be visible.

For that i used sql query as -

select save_visible = case when datediff(month,datefin,getdate())>=1 then cast(0 as bit)   else cast(1 as bit) end

It was working perfectly. But now i want to keep that button visible for previous month till the first 7 days of the current month.

i.e till the 07-06-2014, the button should be visible for previous month (May as per example)

Is there any efficient way to do this with the help of SQL query

Thanks

GarethD · Accepted Answer · 2014-06-25T09:21:49.473

The logic I would employ is something like

WHEN DateFin > CASE WHEN [Today is after the 7th] THEN [1st of This Month]
                    ELSE [First of Last Month]
                END
    THEN 1 
    ELSE 0 
END

So the part that needs solving is getting the right date to compare to. The simplest way of doing this is getting the first of the month, for the day 7 days ago. The standard logic for getting the 1st of the current month is:

SELECT  DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

Or if you prefer a shorter method you can rely on the implicit cast of an int to a date:

SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Then just apply this logic to 7 days ago:

SELECT  DATEADD(MONTH, DATEDIFF(MONTH, '19000101', DATEADD(DAY, -7, GETDATE())), '19000101')

Making your full statement:

save_visible = CASE WHEN DateFin >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', DATEADD(DAY, -7, GETDATE())), '19000101')
                    THEN CAST(1 AS BIT)
                    ELSE CAST(0 AS BIT)
                END

Here's a quick test of this logic

SELECT  Today = d.Date,
        CutOffDate = CAST(DATEADD(MONTH, DATEDIFF(MONTH, '19000101', DATEADD(DAY, -7, d.Date)), '19000101') AS DATE)
FROM    (   SELECT  Date = CAST(DATEADD(DAY, - Number, '20140610') AS DATE)
            FROM    master..spt_values 
            WHERE   [Type] = 'P'
            AND     Number BETWEEN 0 AND 50
        ) AS d

Gives

Today       CutOffDate
2014-06-09  2014-06-01
2014-06-08  2014-06-01
2014-06-07  2014-05-01
2014-06-06  2014-05-01
....
2014-05-08  2014-05-01
2014-05-07  2014-04-01
2014-05-06  2014-04-01
2014-05-05  2014-04-01

score 0 · Answer 2 · answered Jun 25 '14 at 09:10

0

Select save_visible = case when 
  datepart(mm,datefin) = month(getdate()) or datepart(dd,datefin)<=7 then 1 
  else o end

answered Jun 25 '14 at 09:10

Azar

1,852
15
17

1

That doesn't seem to work at all. That would make any row where `datefin` contains a day between 1-7 eligible for saving, rather than being a condition based on the current date. Even if you substitute `getdate()` for `datefin` in the final comparison (which is closer, I think, to the OPs requirements) it then means that, during the first 7 days of any month, *all* rows are eligible for saving, no matter what month they're from. – Damien_The_Unbeliever Jun 25 '14 at 09:32
If it is may , till 7th june it should be visible in may month is that ri8? – Azar Jun 25 '14 at 09:34
Its not working. Its making flag true when the month is same or if the date is within first 7 days of any previous month which is not a requirement – omkar patade Jun 25 '14 at 09:45
Can u pls elaborate the requirement if it is may u want to display the dates till 7 june is that roght? – Azar Jun 25 '14 at 09:46

SQL- Special DATEDIFF condition

2 Answers2