Search code examples
c#t-sqlsap-ase

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?

Solution

  • 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;