CODEWITHSUNDEEP
sql-serverdate
Juan Velez
Juan Velez

Reputation: 741

SQL Date CONVERT

I have the following piece of code that will give me the date 1 month prior to the date i specify. For example if i wanted to get the date of 1 month prior to 11/30/11 the below code will give the result of 2011/10/31

    select CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'11-30-11'),0)), 102)

How can I modify the above code so that it will return the date two months prior to the one i specify so it will return 2011/09/30 if i specify '11-30-11' in the code?

I don't really understand how the above code works? I tried changing the -1 to -2 but that only subtracts 1 day and 1 month so it will return 2011/10/30 in stead of 10/31. I need it to return 2011/09/30 which is 2 months less then the date in the code (11-30-11).

Upvotes: 0

Views: 315

Answers (1)

gbn
gbn

Reputation: 432639

CONVERT(DATETIME, 
         DATEADD(d,-1,
             DATEADD(mm, DATEDIFF(m, 32,'20111130'), 0) -- use yyyymmdd and 32
         )
    , 102)

The DATEADD/DATEDIFF with 0 compares to 01 Jan 1900. So change to 32 to make it 01 Feb 1900

Also, your date format is unsafe. Use yyyymmdd for SQL Server

And probably simpler like this

DATEADD(d, -1, DATEADD(mm, -2, DATEADD(d, 1,'20111130')))

Upvotes: 3

Related Questions