October 30, 2014 at 7:51 am
I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.
) It works just fine if I don't use execute sp_executesql, I am not sure why this is anyone have any ideas?
<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @sqlquery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime
DECLARE @enddate datetime
declare @chkdate nvarchar(25)
SET @startdate = '2014-10-08'
SET @enddate = '2014-10-10'
SET @tablename = 'Arlsq01.DSG.DBO.DICE_ALTROH' + SUBSTRING(CAST(DATEPART(YY, GETDATE()) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM, GETDATE()) as varchar(2)),1,2)
/* Build Transact-SQL String by including the parameter */
SET @sqlquery = '
;WIth hist as(
SELECT DISTINCT alhist.[MASTER_INCIDENT_NTDX],
alhist.[ACCOUNT_NUMBER],
alhist.[MASTER_INCIDENT],
alhd.[ALARM_RESOLUTION],
altyp.[RESOLUTION_DESC],
alhist.[OPR_DELIVERED_TO],
alhist.[OPR_DELIVERED_TIME] delivered_time,
alhist.[TIME_OF_FIRST_CALL],
alhist.[DEALER],
alhist.[TIME_OF_COMPLETION],
alhist.[PENDING_TIME],
alhist.[SQL_LAST_UPDATE] from ' + @tablename
+ ' alhist left outer join [Arlsq01].[DSG].[dbo].[DICE_ALTRANHD] alhd on alhist.account_number = alhd.account_number
left outer join [Arlsq01].[DSG].[dbo].[DICE_ALRESTYP] altyp on alhd.[ALARM_RESOLUTION] = altyp.[ALARM_RESOLUTION]
where convert(datetime, ''10/08/2014'', 101) ' + @startdate + 'between' + @enddate + ')
select hist.[MASTER_INCIDENT_NTDX],
hist.[ACCOUNT_NUMBER],
hist.[MASTER_INCIDENT],
hist.[ALARM_RESOLUTION],
hist.[RESOLUTION_DESC],
hist.[OPR_DELIVERED_TO],
hist.delivered_time,
hist.[TIME_OF_FIRST_CALL],
hist.[DEALER],
hist.[TIME_OF_COMPLETION],
hist.[PENDING_TIME],
hist.[SQL_LAST_UPDATE] from hist
'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @sqlquery, @ParameterDefinition
</code>
October 30, 2014 at 7:58 am
You are trying to append a date to a string.
... where convert(datetime, ''10/08/2014'', 101) ' + @startdate + 'between' + @enddate + ') ...
Convert @startdate and @enddate to strings first.
BTW, you don't need to prefix WITH with a semicolon.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2014 at 8:26 am
Now I am getting this error (Incorrect syntax near '8'.) after I converted the parameters to nvarchar.
October 30, 2014 at 8:34 am
cbrammer1219 (10/30/2014)
Now I am getting this error (Incorrect syntax near '8'.) after I converted the parameters to nvarchar.
How did you convert them?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2014 at 8:35 am
I actually saw another problem with the code
I replaced between with "AND" and put BETWEEN where it should go.
between' + CONVERT(nvarchar(20),@startdate) + ' and+ CONVERT(nvarchar(20),@enddate) + ')
October 30, 2014 at 10:31 am
CONVERT(nvarchar(20),@startdate) + ' and ' + CONVERT(nvarchar(20),@enddate)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply