Syntax error converting datetime from character string.

• Harnessh Gnanasekaran

  Valued Member

  Points: 67

  More actions

  November 6, 2006 at 2:42 am

  #121207

  I am getting this sort of an error when running the following:

  DECLARE @START_DATE varchar,@END_DATE varchar

  SET @START_DATE = '11/01/2006'

  SET @END_DATE = '11/09/2006'

  DECLARE @S_NO int,@L_NO int, @OFF_WORKING_DAYS int,@LEAVE_DETAIL varchar, @i int

  select @S_NO = cast(datepart(dd,@START_DATE) as int)

  select @L_NO = cast(datepart(dd,@END_DATE) as int)

  ERROR

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

  Syntax error converting datetime from character string.

   

   

  When I tried the same seperately however it worked fine:

  DECLARE @TEMP int

  select @TEMP = cast(datepart(dd,'11/01/2006') AS int)

  print @TEMP

  It executes without an error

   

  What could be the problem?

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  November 6, 2006 at 5:09 am

  #670010

  If you define a data type as varchar without specifying a length, you get a varchar of length 1, hence all that you have in your variables is '1'. It's easy to see, run the following

  DECLARE

  @TestStr VARCHAR

  SET @TestStr = 'Hello'

  SELECT @TestStr

  Also note that the date formats dd/mm/yyyy and mm/dd/yyyy are ambiguous. For certaity, use the format yyyy/mm/dd.

  Try the following, with a correction to the data type of your variables and it should work.

  DECLARE

  @START_DATE DATETIME, @END_DATE DATETIME

  SELECT @START_DATE = '2006/11/01', @END_DATE = '2006/11/09'

  DECLARE @S_NO int,@L_NO int, @OFF_WORKING_DAYS int,@LEAVE_DETAIL varchar, @i int

  select @S_NO = cast(datepart(dd,@START_DATE) as int)

  select @L_NO = cast(datepart(dd,@END_DATE) as int)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass