update column char to column datetime

• denimblue

  Mr or Mrs. 500

  Points: 594

  More actions

  February 27, 2008 at 4:30 pm

  #183377

  Table has char column s_dob (35) with m/d/yy data and m/d/yyyy data. Looking for help with update/convert datetime sql query. Thank you.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  February 27, 2008 at 4:45 pm

  #782941

  The cleanest way is to usually just use a secondary column.

  1. create a new column of type Datetime in your table

  2. update the new column with data from s_DOB

  3. drop s_dob

  In T-SQL - that looks something like:

  Go

  alter table MyTable

  add dob datetime

  go

  update MyTable

  set dob=s_dob

  go

  Alter table MyTable

  Drop s_dob

  Run the three separately. It usually gives most control over what's going on (like - if some of the stuff in s_dob is messed up.)

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• denimblue

  Mr or Mrs. 500

  Points: 594

  More actions

  February 28, 2008 at 12:49 pm

  #783413

  Many thanks for your help. I ran it in test and it went smooth. I then ran it all with the live data and of course all is well. I wrote up documentation for the future. Thank you again.:Whistling:

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  February 28, 2008 at 12:57 pm

  #783419

  Good to know it worked - thanks for the feedback!:)

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?