Conversion failed when converting date and/or time from character string

  • Hi guy when i run this Stored procedure it give me message error

    conversion faild when converting date time from charachter sting

    stored procedure as following

    Create proc searchData

    @StartDate datetime,

    @EndDate datetime

    as

    Declare @sqlquery as nvarchar(2000)

    SET @sqlquery ='SELECT * from Employee Where (1=1)'

    If (@StartDate is not NULL) AND (@EndDate is not NULL)

    Set @sqlquery = @sqlquery + ' And (JoinDate

    BETWEEN '+ @StartDate +' AND '+@EndDate+')'

    Exec (@SQLQuery)

    JoinDate found in table Employee as datetime

    but when i make stored procedure as following

    it work in formate dd/mm/yyyy and this is what i need

    ALTER proc [dbo].[searchData]

    @StartDate datetime

    @EndDate datetime,

    as

    select * from dbo.Employee e where JoinDate between @StartDate and @EndDate

    Now what is the proplem in first stored procedure

    Please help me if possible

  • There are 2 problems with your code. You cannot concat datetime data type with varchar data type, so you need to convert your parameters to varchar data type (using convert or cast functions). The second problem is that you need to put apostrophes around the dates. The code bellow shows how to do that. Notice also that your code has the potential to be vulnerable to SQL injection

    declare @StartDate datetime = '20000101'

    declare @EndDate datetime = '20140901'

    declare @sqlquery varchar(200)

    set @sqlquery = 'select * from sys.objects where create_date > '''

    + cast(@StartDate as varchar(20)) + ''' AND create_date < ''' + cast(@EndDate as varchar(20))+ ''''

    select @sqlquery

    exec(@SQLQuery)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply