Coverting char field to datetime

• scorpvo

  SSCommitted

  Points: 1709

  More actions

  July 22, 2003 at 9:35 am

  #80815

  Can anyone help me create a Script to covert a char field that has this: "41CP52RBILL AD00043310 2003-07-032Ingenium" to a datetime "2003-07-03"? I need to covert this field to a Datetime so I can compare it with the current and delete the old records. Thanks for your help.

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  July 22, 2003 at 9:59 am

  #466435

  hi,

  for a quick, one off update you could try the following :-

  select substring("41CP52RBILL AD00043310 2003-07-032Ingenium", charindex("-", "41CP52RBILL AD00043310 2003-07-032Ingenium",0) -4 ,10)

  (replacing the values in quotes with your field name)

  this finds the first - and then removes 4 from the count (to take into account the year) and then just gets the next 10 chars.

  You'll need to convert it to a datetime with the required style.

  Paul

• scorpvo

  SSCommitted

  Points: 1709

  More actions

  July 22, 2003 at 10:25 am

  #466439

  You da Man.. thanks very much..