Transforming varchar to datetime

• Ham

  SSC Veteran

  Points: 262

  More actions

  May 11, 2006 at 10:37 am

  #68520

  I have a table (designed a long time ago by a computer illiterate) which stores datetime fields in varchar data types (e.g. "WED", "SEPT 21, 2003", "In Zone 10 min").

  Now I am required to transfer these fields properly into a new table having the datetime datatypes so I can do date calcuations later on. For example, I have to extract the time from the string "In Zone 10 min" and populate the datetime field in the new table as "00:10:00" or something like that.

  How can I do this with DTS (or without)?

   

  Thanks

   

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  May 11, 2006 at 11:01 am

  #637338

  Well, if you want to use DTS you could use a VB script.

  You could also potentially do this with a T-SQL statement / stored procedure.

  Have a look here:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp

  for date / time functions

  and here:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

  for converting a string to a date / time value.

• Mike Chappell

  SSC-Addicted

  Points: 410

  More actions

  May 11, 2006 at 3:43 pm

  #637410

  For dates like "SEPT 21, 2003" you can also get the Data Pump to do it... edit your data pump, go in the transformations and find the one it's probably auto-created for your date column. Delete the one it's created, select your date column on the left and it's destination column on the right, create a new transformation and select a DateTime one rather than a copy one.

  In the properties of that you can select the source format... aka "MMMM DD, YYYY" and what format you want it to end up as... i.e. "YYYY-MM-DD".

  Otherwise what's mentioned by the poster above, adjusting your source SQL statement to have DateDiffs and Substrings etc.. or VB / ActiveX is quite simple to get into the DTS.

  Cheers,

  Mike

• Bill Scott

  SSCrazy

  Points: 2338

  More actions

  May 12, 2006 at 9:31 am

  #637609

  It looks like you have a nasty job ahead of you.  For those date formats that SQL recognizes you could do something like this, requires two columns in your new table, one for the old data and one for the new:

  update newtable set

      olddatestring = varchardate,

      newdatetime = case isdate(varchardate)

          -- isdate returns 1 when its a recognized date string

          when 1 then convert(datetime, varchardate)

          else null

      end

  then of course, someone has to go in and figure out what date they were trying to input for those that are not recognized as date strings.

  Hope you only have hundreds of records