Convert string date field to Numeric date

• allan audi

  Right there with Babe

  Points: 738

  More actions

  March 22, 2006 at 11:22 pm

  #171006

  How do i convert the string interviewdate field below into numeric date using the t-sql code.  it is a nightmare because the position vary between different months coz some months are longer in length than others e.g November is 8 characters and whereas May is only 3 charatesr therefore the position of the day and year will vary.  Otherwise it would have been easier.  I would highly appreciate if any member has an idea.

  November 07, 2005

  October 24, 2005

  November 07, 2005

  November 29, 2005

  December 19, 2005

  January 04, 2006

  January 16, 2006

  January 30, 2006

  February 01, 2006

  January 30, 2006

  February 27, 2006

  February 27, 2006

  October 31, 2005

  Thnaks Allan.

• [KH]

  SSCommitted

  Points: 1908

  More actions

  March 23, 2006 at 12:31 am

  #628281

  Just convert(datetime, 'xxx') directly

  select convert(datetime, str_date)

  from

  (

  select'November 07, 2005' as str_dateunion all

  select'October 24, 2005' as str_dateunion all

  select'November 07, 2005' as str_dateunion all

  select'November 29, 2005' as str_dateunion all

  select'December 19, 2005' as str_dateunion all

  select'January 04, 2006' as str_dateunion all

  select'January 16, 2006' as str_dateunion all

  select'January 30, 2006' as str_dateunion all

  select'February 01, 2006' as str_dateunion all

  select'January 30, 2006' as str_dateunion all

  select'February 27, 2006' as str_dateunion all

  select'February 27, 2006' as str_dateunion all

  select'October 31, 2005'

  ) d

• [KH]

  SSCommitted

  Points: 1908

  More actions

  March 23, 2006 at 12:33 am

  #628282

  Numeric Date ? what is the format ? Why do you want to store date in numeric, why not using datatime data type ?

  select convert(datetime, str_date), convert(int, convert(varchar(8), convert(datetime, str_date), 112))

  from

  (

  select'November 07, 2005' as str_dateunion all

  select'October 24, 2005' as str_dateunion all

  select'November 07, 2005' as str_dateunion all

  select'November 29, 2005' as str_dateunion all

  select'December 19, 2005' as str_dateunion all

  select'January 04, 2006' as str_dateunion all

  select'January 16, 2006' as str_dateunion all

  select'January 30, 2006' as str_dateunion all

  select'February 01, 2006' as str_dateunion all

  select'January 30, 2006' as str_dateunion all

  select'February 27, 2006' as str_dateunion all

  select'February 27, 2006' as str_dateunion all

  select'October 31, 2005'

  ) d

• allan audi

  Right there with Babe

  Points: 738

  More actions

  March 23, 2006 at 3:02 am

  #628294

  It should be a datetime format and it has work well.Thanks journeyman.  great day.

  allan.