locked
SQL updation RRS feed

  • Question

  • Dear all,

    I have data like below, Date is wrong in many rows, I want to update the year to current Year.

    30032 246    6            2019-08-14 18:50:53.000       NULL

     want update the date to 2010,

    K. Govindaraj

     

     

     

    Saturday, August 28, 2010 2:02 PM

Answers

  • One way would be to use DATEDIFF to find difference in years and then use DATEADD

    UPDATE <TABLE>
    SET <datecolumn> = dateadd(yy,datediff(yy,<datecolumn>, GETDATE()),<datecolumn>) 
    WHERE.....
    

    so based on data you have posted

    declare @t table
    (
     [DATECol] datetime
    
    )
    INSERT INTO @t
    SELECT '2019-08-14 18:50:53.000'
    
    UPDATE @T
    SET DateCol = dateadd(yy,datediff(yy,'2019-08-14 18:50:53.000', GETDATE()),'2019-08-14 18:50:53.000') 
    
    SELECT * FROM @t

     

    Saturday, August 28, 2010 10:58 PM
  • Hi, 

     

    Update Table1 

    Set DateField1 = Convert(DateTime, (Cast(DatePart(Year, DateField1) As Char(4)) + '-' + Cast(DatePart(Month, DateField1) As Char(2)) + '-' + Cast(DatePart(Day, DateField1) As Char(2)) + ' ' + Cast(DatePart(Hour, DateField1) As Char(2)) + ':' + Cast(DatePart(Minute, DateField1) As Char(2)) + ':' + Cast(DatePart(Second, DateField1) As Char(2)) + '.' + Cast(DatePart(MilliSecond, DateField1) As Char(3)))) 

    Where DatePart(Year, DateField1) > DatePart(Year, GetDate())

     

    This will update all the records that their Year is greater than Current Year.


    Best Wishes, Arbi --- Please vote if you find the posting was helpful.
    Monday, August 30, 2010 6:58 PM

All replies

  • One way would be to use DATEDIFF to find difference in years and then use DATEADD

    UPDATE <TABLE>
    SET <datecolumn> = dateadd(yy,datediff(yy,<datecolumn>, GETDATE()),<datecolumn>) 
    WHERE.....
    

    so based on data you have posted

    declare @t table
    (
     [DATECol] datetime
    
    )
    INSERT INTO @t
    SELECT '2019-08-14 18:50:53.000'
    
    UPDATE @T
    SET DateCol = dateadd(yy,datediff(yy,'2019-08-14 18:50:53.000', GETDATE()),'2019-08-14 18:50:53.000') 
    
    SELECT * FROM @t

     

    Saturday, August 28, 2010 10:58 PM
  • Hi, 

     

    Update Table1 

    Set DateField1 = Convert(DateTime, (Cast(DatePart(Year, DateField1) As Char(4)) + '-' + Cast(DatePart(Month, DateField1) As Char(2)) + '-' + Cast(DatePart(Day, DateField1) As Char(2)) + ' ' + Cast(DatePart(Hour, DateField1) As Char(2)) + ':' + Cast(DatePart(Minute, DateField1) As Char(2)) + ':' + Cast(DatePart(Second, DateField1) As Char(2)) + '.' + Cast(DatePart(MilliSecond, DateField1) As Char(3)))) 

    Where DatePart(Year, DateField1) > DatePart(Year, GetDate())

     

    This will update all the records that their Year is greater than Current Year.


    Best Wishes, Arbi --- Please vote if you find the posting was helpful.
    Monday, August 30, 2010 6:58 PM