none
Syntax error converting datetime from character string.

    Question

  • hi
    i'm getting this error but i don't know why it happens, this is my code, the date come to the stored procedure as varchar
    ALTER       PROCEDURE FindRestrictedImportedMat
    @STATUS AS integer Output,
       @CompanyName as  VARCHAR(100),
       @indeces as varchar(50) ,
       @fromDate as varchar(10),
       @toDate as varchar(10)
    
    AS
    begin
    
    
    set @STATUS = 1
    
    
    declare @dFrom as datetime;
    declare @dTo as datetime;
    
    if @fromDate is not  null
    begin
    	set @dFrom = convert(datetime,  @fromDate, 103 );
    
    end
    else
    begin
    	set @dFrom = null;
    end
    
    if @toDate is not null
    begin
    	set @dTo = convert(datetime,  @toDate, 103 );
    end
    else
    begin
    	set @dTo = null;
    end
    
    declare @statement nvarchar(4000)
    select 0	
    
    set @statement= 'select INVOICE_NO,  MOH_IMPORT_APPLIC.IMP_APPLIC_NO, MOH_IMPORT_APPLIC.IMP_APPLIC_SUB, MOH_IMPORT_APPLIC.IMP_APPLIC_DATE, UNIT_ID, IMP_AMOUNT, MATERIAL_STATUS, SCIENTIFIC_NAME from MOH_NATIONAL_REGISTER,  MOH_IMPORT_APPLIC,  MOH_IMPORTED_MATERIAL where  MOH_NATIONAL_REGISTER.MOH_COMPANY_ID = MOH_IMPORT_APPLIC.MOH_COMPANY_ID and MOH_IMPORT_APPLIC.IMP_APPLIC_NO = MOH_IMPORTED_MATERIAL.IMP_APPLIC_NO and MOH_IMPORT_APPLIC.IMP_APPLIC_DATE = MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE and MOH_IMPORT_APPLIC.IMP_APPLIC_SUB = MOH_IMPORTED_MATERIAL.IMP_APPLIC_SUB  and  MATERIAL_TYPE = ''1'' AND (MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE between isnull(MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE,  ' + @dFrom + ' ) and  isnull(MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE, ' + @dTo + ' ) ) AND MATERIAL_STATUS in (' + @indeces  + ')  and (COMPANY_NAME like ''' + @CompanyName + ''')';
    execute (@statement)
    set @STATUS = 0
    
    
    
    if (@@error<>0)
    	begin
    		set @STATUS=1
    		return
    	end
    end
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    can you please help me
    this error always shows up
    Syntax error converting datetime from character string.
    Sunday, June 28, 2009 4:48 PM

Answers

  • hi
    sorry for my late replay
    the error i was getting is the one i wrote above Syntax error converting datetime from character string
    well i solved the problem by passing varchar parameters to SP instead of date and comparing this varchar parameters with the date and it worked fine

    :)

    Wednesday, July 8, 2009 6:28 PM

All replies

  • Hi Mahmoud,

    My guess is that you're providing a date in the format dd/mm/yy, but you're telling SQL Server that you're passing in dd/mm/yyyy (which is what 103 does).  Try the following code:

    declare @dFrom datetime
    declare @fromDate varchar(10)
    select @fromDate = '05/01/09'
    
    set @dFrom = convert(datetime,  @fromDate, 103 );
    select @dFrom

    As you can see, you get the same error.  If you change @fromDate to '05/01/2009' (dd/mm/yyyy) you successfully get a date.  Alternatively, if you change the 103 to 3, you'll also get the date.

    I strongly recommend that you always pass in dates in the format yyyy-mm-dd (style 112), as there is no ambiguity as to what you mean by the date, no matter what country you're in.

    Sunday, June 28, 2009 11:56 PM
    Answerer
  • I can second that, beside the fact that format 112 is yyyymmdd, which is the ISO format and is much easier to handle as you can do simple calculations with it, comparison is easier and it is supported by almost every front-/backend.

    Beside that, I do not get the point why you are using dynamic sql here, this will cause you plan caches (depending on the version of SQL Server) will cache the plans for that query which mihgt be never reusable. I would consider changing that to plain SQL and resolve the IN operator with a Split function.

    Split Function (Jens Suessmeyer)
       http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

     
    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Monday, June 29, 2009 5:31 AM
    Moderator
  • thank you both, by the way i'm using sqlserver 2000.
    when i changed the year to 2 digits i gmot an error on the conversion line i mnea this line:

    set @dFrom = convert(datetime,  @fromDate, 103 );
    i set the value of @fromDate = '10/10/10' but ti carxhes on this line,
    but the code that i wrote previously wascraching on this line:

    set @statement= 'select INVOICE_NO,  MOH_IMPORT_APPLIC.IMP_APPLIC_NO, MOH_IMPORT_APPLIC.IMP_APPLIC_SUB, MOH_IMPORT_APPLIC.IMP_APPLIC_DATE, UNIT_ID, IMP_AMOUNT, MATERIAL_STATUS, SCIENTIFIC_NAME from MOH_NATIONAL_REGISTER,  MOH_IMPORT_APPLIC,  MOH_IMPORTED_MATERIAL where  MOH_NATIONAL_REGISTER.MOH_COMPANY_ID = MOH_IMPORT_APPLIC.MOH_COMPANY_ID and MOH_IMPORT_APPLIC.IMP_APPLIC_NO = MOH_IMPORTED_MATERIAL.IMP_APPLIC_NO and MOH_IMPORT_APPLIC.IMP_APPLIC_DATE = MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE and MOH_IMPORT_APPLIC.IMP_APPLIC_SUB = MOH_IMPORTED_MATERIAL.IMP_APPLIC_SUB  and  MATERIAL_TYPE = ''1'' AND (MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE between isnull(MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE,  ' + @dFrom + ' ) and  isnull(MOH_IMPORTED_MATERIAL.IMP_APPLIC_DATE, ' + @dTo + ' ) ) AND MATERIAL_STATUS in (' + @indeces  + ')  and (COMPANY_NAME like ''' + @CompanyName + ''')';

    i think the error was that i'm trying to concatinate date to string, i think this was the erro but i could not resolve it, because i have to use this way to excute my sql statment.

    jens asked me why not use the split function to handle the in issue. YES you are right i used this way to handle the in condition but this does not mean that i didn't try the split function and then filling the data in table variable then getting them using subquery, i used it and it successed in the query analizaer but when i call the SP from java i got null in the resultset, therefore i forgot about using the split function and the table variable, i used this method and everything was normal and good.
    just yesterday i needed to add condition for the date then the problem arised. i said the when i degu it the code crash on the line where the date variable is concatinated with the string statment.

    i don't know how to do this, if someone can solve it in another way i will be gratful, it's not nessecary this way, what i need is gettign the result, if you jens can solve it  using the split function, i will be very happy, as i told you the query anlizaer give me result but the resultset returned is null when i see it in java code

    many thanks

    as i said i'm using sqlserver 2000
    Monday, June 29, 2009 6:01 AM
  • What was the problem / exception message from the Java code ? And why did you use a two digit year ? We suggested you to use the yyyymmdd format instead, as this will be almost always convertible.

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Monday, June 29, 2009 6:39 AM
    Moderator
  • hi
    sorry for my late replay
    the error i was getting is the one i wrote above Syntax error converting datetime from character string
    well i solved the problem by passing varchar parameters to SP instead of date and comparing this varchar parameters with the date and it worked fine

    :)

    Wednesday, July 8, 2009 6:28 PM