CODEWITHSUNDEEP
sqlsql-server-2008sql-date-functions
sudhakarssd
sudhakarssd

Reputation: 451

Using date functions in SQL Server 2008

I need results where if end_dt is less than the 15th of a month it should consider the previous month date

e.g - if end_dt is 08-May-2011 than it should consider as 30-APR-2011

I have used following code but is there any better way of doing it

select
    id, 
    start_dt, 
    end_dt,
    End_mth = 
        case
            when end_dt<=DATEADD(YY,(DATEPART(yy,end_dt))-1900,0) + DATEADD(MM,(DATEPART(MM,end_dt))-1,0)+ 15 then month(DATEADD (mm,-1,end_dt))
            else month(isnull(end_dt,'31-Mar-2012'))
        end
from 
    sd_table

Upvotes: 1

Views: 468

Answers (1)

Vikdor
Vikdor

Reputation: 24124

if end_dt is less than 15th of a month it should consider the previous month date e.g - if end_dt is 08-May-2011 than it should consider as 30-APR-2011

The following would address this: 

SELECT
    id, 
    start_dt, 
    end_dt,
    CASE
        WHEN DATEPART(dd, end_dt) < 15 THEN DATEADD(dd, -1 * DATEPART(dd, end_dt), end_dt)
        ELSE end_dt
    END as End_mth
from 
    sd_table

Upvotes: 1

Related Questions