variable in openquery err: Conversion failed when converting date

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  July 10, 2014 at 1:55 pm

  #296432

  Hi using the following code, I'm receiving error:

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

  DECLARE @DAYFROM DATETIME

  DECLARE @DAYTO DATETIME

  DECLARE @SQL VARCHAR(1000)

  SET @DAYFROM = CAST ( ((GETDATE() - DATEPART(weekday,GETDATE()) ) - 98) AS VARCHAR(12))

  SET @DAYTO = GETDATE()

  SET @SQL = 'SELECT * from openquery (DVADMSP2, ''select * from AGENT_VIEW WHERE ORDER_CREATE_DATE = TOCHAR(''''' + @DAYFROM + ''''')'')'

  PRINT (@SQL)

  Is it my quotes out of sync?

• Sean Lange

  SSC Guru

  Points: 286562

  More actions

  July 10, 2014 at 2:01 pm

  #1728508

  Marcus Farrugia (7/10/2014)

  ---

  Hi using the following code, I'm receiving error:

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

  DECLARE @DAYFROM DATETIME

  DECLARE @DAYTO DATETIME

  DECLARE @SQL VARCHAR(1000)

  SET @DAYFROM = CAST ( ((GETDATE() - DATEPART(weekday,GETDATE()) ) - 98) AS VARCHAR(12))

  SET @DAYTO = GETDATE()

  SET @SQL = 'SELECT * from openquery (DVADMSP2, ''select * from AGENT_VIEW WHERE ORDER_CREATE_DATE = TOCHAR(''''' + @DAYFROM + ''''')'')'

  PRINT (@SQL)

  Is it my quotes out of sync?

  No your quotes seem to be fine. Notice the error message. This is happening when you create the string. That is because you have @DAYFROM defined as a datetime so when you include in your SET it is going to try to convert the string data to a datetime. Change your @DAYFROM and it will work.

  DECLARE @DAYFROM varchar(12)

  DECLARE @DAYTO DATETIME

  DECLARE @SQL VARCHAR(1000)

  SET @DAYFROM = CAST ( ((GETDATE() - DATEPART(weekday,GETDATE()) ) - 98) AS VARCHAR(12))

  SET @DAYTO = GETDATE()

  SET @SQL = 'SELECT * from openquery (DVADMSP2, ''select * from AGENT_VIEW WHERE ORDER_CREATE_DATE = TOCHAR(''''' + @DAYFROM + ''''')'')'

  PRINT (@SQL)

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/