Using date functions in SQL Server 2008

Question

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

Vikdor · Accepted Answer · 2012-09-22 08:47:40Z

1

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

answered Sep 22, 2012 at 8:47

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using date functions in SQL Server 2008

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related