CODEWITHSUNDEEP
sqlsql-serverdateadd
astroboy
astroboy

Reputation: 59

How to add year and end of month in sql query

Mabuhay!

Is there any shortcode for updating date with additional 2 years and end of month?

Sample: Account Opened: 2017-04-04
Expiry: 2019-04-30

UPDATE dbname SET [Expiry] = DateAdd(year,2,[AccountOpened])

Without adding this UPDATE dbname SET [Expiry] = DateAdd(mm,1,[AccountOpened]) because it will add 1 month instead of last month date.

Thanks

Upvotes: 1

Views: 1956

Answers (3)

felipe llantos
felipe llantos

Reputation: 1

Are you looking for this query?

Where Clause = CAST([CustomDate2] AS DATE) between CAST(GETDATE() AS DATE) and DATEADD(day, +/-number here,CAST(GETDATE() AS DATE))

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,"your date")+1,0))

LastDay_AnyMonth ResultSet: LastDay_AnyMonth ———————–

2007-08-31 23:59:59.000

Upvotes: 0

Jcl
Jcl

Reputation: 28272

This should work for Sql Server (any version, I think)

UPDATE dbName
SET [Expiry] = DATEADD(MONTH, ((YEAR([AccountOpened]) - 1898) * 12) + MONTH([AccountOpened]), -1)

I made a fiddle

For Sql Server 2012+ you can simply:

UPDATE dbName SET [Expiry] = EOMONTH([AccountOpened], 24)

For MySql (which I guess you are not using because you are using brackets for your fields, but here it is nonetheless):

UPDATE dbName SET Expiry = LAST_DAY(DATE_ADD(AccountOpened, INTERVAL 2 YEAR))

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

If your are using SQLSERVER following script will give desire result. Before performing UPDATE operation take back of Original data.

UPDATE dbname 
SET [Expiry] = DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0, (DATEADD(YY,2,AccountOpened))) + 1,0))

Upvotes: 1

Related Questions