Can''t convert a character string into datetime

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 5, 2007 at 7:04 pm

  #117473

  Hi,

  I am fetching DOS file list into a temp table togethere with some dates:

  select finfo from #t

  ----------------------------------------------------

  finfo

  01/07/2005  01:26 PM    <DIR>          _TD6108

  04/07/2005  01:00 PM    <DIR>          _TD6104

  21/11/2005  03:48 PM    <DIR>          _TD7208

  17/01/2006  09:52 AM    <DIR>          _TD5544

  06/02/2006  04:02 PM    <DIR>          _TD7644

  08/03/2006  04:41 PM    <DIR>          _TD7820

  ...

  Than I am trying to convert the first 20 or 10 characters into datetime format:

  select convert(datetime, left(finfo, 20)) from #t

  ----------------------------------------------------

  Server: Msg 241, Level 16, State 1, Line 1

  Syntax error converting datetime from character string.

  select convert(datetime, left(finfo, 10)) from #t

  ----------------------------------------------------

  Server: Msg 241, Level 16, State 1, Line 1

  Syntax error converting datetime from character string.

  I was trying to use cast but the results are the same.  Is there something I am doing wrong?

  Thanks.

   

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  February 5, 2007 at 7:28 pm

  #687899

  By default, SQL recognizes mm/dd/yy.  However put the following statement before your query and it should work:

  SET DATEFORMAT DMY

  Or you can use:

  select convert(datetime, left(finfo,20), 103) frpm #t

  Thanks for the question and the brilliant answers in the thread referenced below.  I learned something today.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=332133

  Russel Loski, MCSE Business Intelligence, Data Platform