CODEWITHSUNDEEP
sqlsql-server-2008datediff
omkar patade
omkar patade

Reputation: 1591

SQL- Special DATEDIFF condition

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

Upvotes: 0

Views: 1215

Answers (2)

GarethD
GarethD

Reputation: 69759

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

Upvotes: 2

Azar
Azar

Reputation: 1867

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

Upvotes: 0

Related Questions