CODEWITHSUNDEEP
c#t-sqlsap-ase
User11040
User11040

Reputation: 218

Update 2 million rows for 1 column

I have a table with approximately 2 million records. I have to loop through each record and update the effective date. I need to set the day to the first of the month for each date.

If the current date is the first of the month, then ignore.

i.e.

    07/01/2018    
    07/21/2018 => 07/01/2018
    08/11/2018 => 08/01/2018

Currently, I'm writing this as a C# program and it taking way too long. Is there a better solution?

Upvotes: 0

Views: 275

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23827

It could be as simple as:

Update myTable 
    set myDate = DateAdd(day, 1-Day(myDate), myDate)
where day(myDate) > 1;

Upvotes: 0

Squirrel
Squirrel

Reputation: 24793

Just use DATEADD() and DATEDIFF() combination to get the first of the month date

UPDATE t
SET    datecol = DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', datecol), '1900-01-01')
FROM   yourtable t;

Upvotes: 8

Related Questions