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

    Question

  • Hi,

    I created LinkedServer in SSMS from MYSQL DB.

    When i Execute below open Query its working Fine

    SELECT * FROM OPENQUERY(MySQL,'call GASP_sales_aps(''2013-04-01 00:00:00'',''2013-05-01 23:23:59'')')

    But my problem is when i declared variable and execute below am getting error

    declare @dt_start datetime='2013-04-01 00:00:00'
    declare @dt_end datetime='2013-05-01 23:23:59'
    declare @op datetime
    set @op='SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps(''+@dt_start+'',''+@dt_end+''))'
    exec(@op)

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

    Can anyone help me where am going wrong?

    Friday, May 17, 2013 7:35 AM

Answers

  • This happens, because when two data types meet in SQL Server, SQL Server converts the type with lower precedence to the type with higher precedence. Datetime has higher precedence than varchar, why SQL Server attempts to convert the string 'SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps(' to datetime, which of course ends in tears.

    Here is a solution that is tested so that it compiles on the SQL Server side:

    declare @dt_start datetime ='2013-04-01 00:00:00'
    declare @dt_end datetime = '2013-05-01 23:23:59'
    declare @call nvarchar(128) =
        'call GASP_sales_aps(' +
        quotename( convert(varchar(23), @dt_start, 121), '''') + ',' +
        quotename( convert(varchar(23), @dt_end, 121), '''') + ')'

    declare @op nvarchar(MAX)

    set @op = 'SELECT * FROM OPENQUERY(MySQL, ' + quotename(@call, '''') + ')'
    print @op
    exec(@op)

    Beware that before you start using quotename all over town that it is limited to an input of 128 characters, and will truncate longer strings.
    See here for an alternate solution: http://www.sommarskog.se/dynamic_sql.html#quotestring

    It is necessary to build a dynamic OPENQUERY this way, unless you want to go insane with all those single quotes.

    An alternative is:

      EXEC ('CALL GASP_sales_aps(?, ?'), @dt_start, @dt_end) AT MySQL

    simpler, but you cannot join to local tables this way. Then again, given how messy the dynamic query is, it's easier to insert into a local temp table anyway.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 17, 2013 8:30 AM
  • Why @op is declared as Datetime. It should be nvarchar(MAX). MAX is size nothing but.

    Try the below:

    declare @dt_start datetime='2013-04-01 00:00:00'
    declare @dt_end datetime='2013-05-01 23:23:59'
    declare @op varchar(1000)
    set @op='SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps
    '''+Cast(@dt_start as varchar(50))+''','''+Cast(@dt_end as Varchar(50))+''')'
    exec(@op)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 17, 2013 7:47 AM

All replies

  • Hi,

    I created LinkedServer in SSMS from MYSQL DB.

    When i Execute below open Query its working Fine

    SELECT * FROM OPENQUERY(MySQL,'call GASP_sales_aps(''2013-04-01 00:00:00'',''2013-05-01 23:23:59'')')

    But my problem is when i declared variable and execute below am getting error

    declare @dt_start datetime='2013-04-01 00:00:00'
    declare @dt_end datetime='2013-05-01 23:23:59'
    declare @op datetime
    set @op='SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps(''+@dt_start+'',''+@dt_end+''))'
    exec(@op)

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

    Can anyone help me where am going wrong?

    Friday, May 17, 2013 7:28 AM
  • Hello,

    You should use either the ODBC syntax

    declare @dt_start datetime = {ts N'2013-04-01 00:00:00'}
    declare @dt_end datetime = {ts N'2013-05-01 23:23:59'}

    or use the CONVERT function with format option 120 = ISO format:

    declare @dt_start datetime = CONVERT(datetime, N'2013-04-01 00:00:00', 120)
    declare @dt_end datetime = CONVERT(datetime, N'2013-05-01 23:23:59', 120)


    Olaf Helper

    Blog Xing


    Friday, May 17, 2013 7:40 AM
  • Why @op is declared as Datetime. It should be nvarchar(MAX). MAX is size nothing but.

    Try the below:

    declare @dt_start datetime='2013-04-01 00:00:00'
    declare @dt_end datetime='2013-05-01 23:23:59'
    declare @op varchar(1000)
    set @op='SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps
    '''+Cast(@dt_start as varchar(50))+''','''+Cast(@dt_end as Varchar(50))+''')'
    exec(@op)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 17, 2013 7:47 AM
  • Hi Olaf,Am Getting same Error ..
    Friday, May 17, 2013 7:51 AM
  • Are you getting the same error after changing @op datatype from Datetime to nvarchar(MAX)?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 17, 2013 8:09 AM
  • Why @op is declared as Datetime. It should be nvarchar(MAX


    Right, that's the point I missed.

    Olaf Helper

    Blog Xing

    Friday, May 17, 2013 8:17 AM
  • This happens, because when two data types meet in SQL Server, SQL Server converts the type with lower precedence to the type with higher precedence. Datetime has higher precedence than varchar, why SQL Server attempts to convert the string 'SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps(' to datetime, which of course ends in tears.

    Here is a solution that is tested so that it compiles on the SQL Server side:

    declare @dt_start datetime ='2013-04-01 00:00:00'
    declare @dt_end datetime = '2013-05-01 23:23:59'
    declare @call nvarchar(128) =
        'call GASP_sales_aps(' +
        quotename( convert(varchar(23), @dt_start, 121), '''') + ',' +
        quotename( convert(varchar(23), @dt_end, 121), '''') + ')'

    declare @op nvarchar(MAX)

    set @op = 'SELECT * FROM OPENQUERY(MySQL, ' + quotename(@call, '''') + ')'
    print @op
    exec(@op)

    Beware that before you start using quotename all over town that it is limited to an input of 128 characters, and will truncate longer strings.
    See here for an alternate solution: http://www.sommarskog.se/dynamic_sql.html#quotestring

    It is necessary to build a dynamic OPENQUERY this way, unless you want to go insane with all those single quotes.

    An alternative is:

      EXEC ('CALL GASP_sales_aps(?, ?'), @dt_start, @dt_end) AT MySQL

    simpler, but you cannot join to local tables this way. Then again, given how messy the dynamic query is, it's easier to insert into a local temp table anyway.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 17, 2013 8:30 AM
  • Hi Olaf,

    Am getting

    Incorrect syntax near 'call'

    Friday, May 17, 2013 8:45 AM
  • Hi RAvuri, Please follow Erland's suggestion. Erland had given you much better/safe solution.

    The miss was '()' in the previous thread.

    declare @dt_start datetime='2013-04-01 00:00:00'
    declare @dt_end datetime='2013-05-01 23:23:59'
    declare @op varchar(1000)
    set @op='SELECT * FROM OPENQUERY(MySQL,call GASP_sales_aps
     ('''+Cast(@dt_start as varchar(50))+''','''+Cast(@dt_end as Varchar(50))+')''))'
    --Print @op
    exec(@op)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 17, 2013 9:03 AM
  • Hi Lateesh,

    Sorry for late reply...

    Finally i got solution what i have follow to u.

    Wednesday, May 22, 2013 7:48 AM