Syntax error converting datetime from character string.

• Harnessh Gnanasekaran

  Valued Member

  Points: 67

  More actions

  November 6, 2006 at 2:42 am

  #153274

  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?

• ijaz

  SSCommitted

  Points: 1912

  More actions

  November 6, 2006 at 3:58 am

  #669996

  Hi,

  The problem in your code is that you declare the variable for the date as varchar. By default if you did not specify the length of the varchar parameter then it take one.

  You need to correct the line

  DECLARE @START_DATE Varchar (22) , @END_DATE varchar (22)

  Then your code will work fine.

  Hope this will slove your problem

   

  cheers

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 6, 2006 at 4:18 am

  #669997

  There is no datatype "varchar"

  DECLARE @START_DATE varchar,@END_DATE varchar

  actually means

  DECLARE @START_DATE varchar(1) ,@END_DATE varchar(1)

  And it's not so easy to understand why you declare date variables as varchar, not datetime?

  Just to create extra load for your server on implicit conversions?

  _____________
  Code for TallyGenerator