Update only year

• dannyfirst80

  SSC Veteran

  Points: 294

  More actions

  November 30, 2018 at 5:15 am

  #378721

  Hi ya,
  I have a scenario where date column data type is nvarchar and year is not entered correctly. I want to update the year correctly can you please help me?

  20-02-0002
  16-05-0001
  21-01-0015

  Expected Output:
  20-02-2002
  16-05-2001
  21-01-2015

  Thanks,
  Danny

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 30, 2018 at 5:21 am

  #2015090

  Danny

  Use REPLACE to replace '-00' with '-20'.

  Edit: and then change the data type to date so you don't get this happening again.

  John

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 30, 2018 at 5:48 am

  #2015093

  John Mitchell-245523 - Friday, November 30, 2018 5:21 AM

  Edit: and then change the data type to date so you don't get this happening again.

  Definitely the most important thing to do after fixing the data. You can easily achieve this with an ALTER statement:
  ALTER TABLE {Your Table} ALTER COLUMN {Date Column} date;
  Obviously you'll need to replace the text in braces ({}) with the relevant object names.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk