convert varchar to datetime

• j.grimanis

  SSC Eights!

  Points: 852

  More actions

  July 23, 2009 at 6:48 am

  #156007

  Hi,

  I apologize if someone has asked this again in past, but it is something urgent.

  I have a varchar(50) field like this ' 23/6/2009 11:20:25 πμ' .

  I am trying to convert it to datetime with the following tsql :

  SELECT top 5 convert( datetime,(SUBSTRING(adate,1,9)) ) from comm where adate is the field and comn the table.

  I got this message: Msg 242, Level 16, State 3, Line 2

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  I would appreciate if someone could help me.

  Thanks,

  John

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  July 23, 2009 at 7:05 am

  #1028922

  Try something like this

  DECLARE @d AS VARCHAR(50)

  SET @d = '26/05/2009 15:15:15'

  SELECT CONVERT(DATETIME,SUBSTRING(@d,1,10),103)

  The 103 tells convert the format of the date.

  But notice that in your example data the length in the substring function will need to vary. You can get round this using CHARINDEX to find the first space and subtract 1.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 23, 2009 at 3:58 pm

  #1029336

  You can also use Set DateFormat prior to the conversion. Like this:

  Set DateFormat dmy;

  DECLARE @d AS VARCHAR(50)

  SET @d = '26/05/2009 15:15:15'

  Select CONVERT(datetime, @d);

  Go

  The dateformat is part of the OS's language setting and us_english by default uses mdy.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  July 24, 2009 at 3:42 am

  #1029534

  Hmm, didn't know that.