Varchar column conversion

• mugahjoseph

  SSC Enthusiast

  Points: 117

  More actions

  April 19, 2010 at 2:44 am

  #146585

  Hi,

  I havve a table with a column dateofbirth that is stored as Varchar.

  It has values like 02/25/2009, March 23rd 2008, and Wednesday April 2nd 2009.

  I need to convert this column to data type datetime.

  Any idea that can assist?

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  April 19, 2010 at 2:54 am

  #1153655

  Please look into the following ones

  If the varchar contains Day [Mon or tue..etc] or rd/nd/th [2nd ,12th,23rd] it doesnt allow to use cast ...

  SELECT CAST ('02/25/2009' as DATETIME)

  SELECT CAST ('March 23 2008' as DATETIME)

  SELECT CAST ('April 2 2009' as DATETIME)

• Tom257

  SSChampion

  Points: 11543

  More actions

  April 19, 2010 at 3:27 am

  #1153666

  You may have to strip out the 'nd', 'rd' from April 2nd, April 3rd etc using REPLACE.

  See CAST AND CONVERT in MSDN for a full description