CODEWITHSUNDEEP
sql-server
bajivali shaik
bajivali shaik

Reputation: 81

How to bulk update the date column

I have a table with 10000 records.

Sample

Id  Transaction_Id  Contract_Id Contractor_Id   ServiceDetail_Id    ServiceMonth    UnitsDelivered  CreateDate
----------------------------------------------------------------------------------------------------------------------------
1   1   352 466 590 2016-03-01  203 2016-04-25 17:01:55.000
2   1   352 466 566 2016-03-01  200 2016-04-25 17:02:38.807
3   1   352 466 138 2016-04-13  20  2016-04-13 00:00:00.000
5   1   352 466 138 2016-04-14  21  2016-04-13 00:00:00.000
6   10011   40  460 68  2016-03-17  10  2016-04-25 17:20:13.413
7   10011   40  460 511 2016-03-17  15  2016-04-25 17:20:13.413
8   10011   40  460 1611    2016-03-17  20  2016-04-25 17:20:13.413
9   20011   352 466 2563    2016-02-05  10  2016-04-25 17:20:25.307
11  100 40  460 68  2016-03-17  10  2016-04-25 17:29:23.653

In this table I have servicemonth with different dates.

I want to update the servicemonth column to the existing months last date.

Can anyone suggest a single query to update this?

Upvotes: 0

Views: 173

Answers (2)

Vigya
Vigya

Reputation: 142

Update  servicemonth  
set servicemonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

--replace getdate() with the date column for which you want the end day of the month

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294307

EOMONTH: Returns the last day of the month that contains the specified date, with an optional offset.

UPDATE ... SET servicemonth = EOMONTH(servicemonth)

Upvotes: 1

Related Questions