sql code to change the given date to the last day of the previous month

  Hi ,

    I need to check in my SQL stored procedure , if the input date is 3 months before now
    then set it to last day of the previous month of the given date.
    Ex: if the input date parameter is 02/12/2010,  i need to set it to 01/31/2010
   Can you please help me how o check the input date, if it is 3 months before now
   and how to set it to the previous month last day of the given input date.

Who is Participating?

declare @inputDate datetime
set @inputDate = '2011-02-12'

if dateDiff(m, @inputDate, getDate()) >= 3
	set @inputDate = dateAdd(d, -1, convert(datetime, convert(varchar(6), @inputDate, 112) +'01', 112))

select @inputDate

Open in new window

SharathData EngineerCommented:
Check this/
declare @date datetime
set @date = '02/12/2010'
select case when DATEDIFF(MONTH,@date,GETDATE()) >3 
            then DATEADD(dd,-1,CONVERT(datetime,CONVERT(varchar(6),@date,112)+'01'))
            else @date end -- 2010-01-31 00:00:00.000

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.