September 27, 2018 at 8:24 am
Hi
Using query_string, I am trying to run below statement and getting error..
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);
Error is -> Conversion failed when converting date and/or time from character string.
September 27, 2018 at 9:06 am
What are you trying to do? STARTTIME is already a datetime. Why are you casting it as a datetime?
September 27, 2018 at 9:11 am
I get error with below statement so I tried casting...
SET @SQLSTR='
insert into #TEMP1(date_time) values( ' + @STARTTIME + ')';
September 27, 2018 at 9:12 am
mahnj18 - Thursday, September 27, 2018 8:24 AMHi
Using query_string, I am trying to run below statement and getting error..CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);Error is -> Conversion failed when converting date and/or time from character string.
First, why are you using dynamic SQL for this?
September 27, 2018 at 9:16 am
mahnj18 - Thursday, September 27, 2018 8:24 AMHi
Using query_string, I am trying to run below statement and getting error..CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);Error is -> Conversion failed when converting date and/or time from character string.
Try this:
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)=''
,@SQLParm NVARCHAR(MAX) = N'inStartTime DATETIME';
SET @SQLSTR='insert into #TEMP1(date_time) values( @inStartTime )';
PRINT(@SQLSTR);
EXEC sys.sp_executesql @SQLSTR, @SQLParm, @inStartTime = @STARTTIME;
September 27, 2018 at 9:17 am
RonKyle - Thursday, September 27, 2018 9:06 AMWhat are you trying to do? STARTTIME is already a datetime. Why are you casting it as a datetime?
SQL Server is getting confused with its implicit conversion and instead of trying to convert the date time to string and concatenate it's trying to convert the strings to date times and add them.
September 27, 2018 at 9:21 am
I have some code in production wherein I am referring different databases and accessing their tables.
so making use of 'USE database' statement and then inserting many columns data into its tables . and that insert statement has one 'datetime' column for which I get this type of error. All this is present in a query string.
Since I cannot put my prod code here. So simulated it into a simple case..
September 27, 2018 at 9:34 am
Lynn Pettis - Thursday, September 27, 2018 9:16 AMmahnj18 - Thursday, September 27, 2018 8:24 AMHi
Using query_string, I am trying to run below statement and getting error..CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);Error is -> Conversion failed when converting date and/or time from character string.
Try this:
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)=''
,@SQLParm NVARCHAR(MAX) = N'inStartTime DATETIME';SET @SQLSTR='insert into #TEMP1(date_time) values( @inStartTime )';
PRINT(@SQLSTR);
EXEC sys.sp_executesql @SQLSTR, @SQLParm, @inStartTime = @STARTTIME;
I get below error..
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'inStartTime'.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@inStartTime".
September 27, 2018 at 9:40 am
Lynn Pettis - Thursday, September 27, 2018 9:16 AMmahnj18 - Thursday, September 27, 2018 8:24 AMHi
Using query_string, I am trying to run below statement and getting error..CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);Error is -> Conversion failed when converting date and/or time from character string.
Try this:
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)=''
,@SQLParm NVARCHAR(MAX) = N'inStartTime DATETIME';SET @SQLSTR='insert into #TEMP1(date_time) values( @inStartTime )';
PRINT(@SQLSTR);
EXEC sys.sp_executesql @SQLSTR, @SQLParm, @inStartTime = @STARTTIME;
This works fine , I added '@' before inStartTime -> @SQLParm NVARCHAR(MAX) = N'@inStartTime DATETIME';
September 27, 2018 at 10:03 am
mahnj18 - Thursday, September 27, 2018 9:40 AMLynn Pettis - Thursday, September 27, 2018 9:16 AMmahnj18 - Thursday, September 27, 2018 8:24 AMHi
Using query_string, I am trying to run below statement and getting error..CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(' + @STARTTIME + ' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);Error is -> Conversion failed when converting date and/or time from character string.
Try this:
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)=''
,@SQLParm NVARCHAR(MAX) = N'inStartTime DATETIME';SET @SQLSTR='insert into #TEMP1(date_time) values( @inStartTime )';
PRINT(@SQLSTR);
EXEC sys.sp_executesql @SQLSTR, @SQLParm, @inStartTime = @STARTTIME;This works fine , I added '@' before inStartTime -> @SQLParm NVARCHAR(MAX) = N'@inStartTime DATETIME';
Good. That was a copy/paste error while I was typing my code.
September 28, 2018 at 8:54 am
The problem is that you're trying to concatenate a datetime to a string. Cast @STARTTIME as an nvarchar and add single quotes inside @SQLSTR, and your code will work.
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';
SET @SQLSTR='insert into #TEMP1(date_time) values( cast(''' + cast(@STARTTIME as nvarchar(50)) + ''' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);
September 28, 2018 at 9:13 am
wmg - Friday, September 28, 2018 8:54 AMThe problem is that you're trying to concatenate a datetime to a string. Cast @STARTTIME as an nvarchar and add single quotes inside @SQLSTR, and your code will work.
CREATE TABLE #TEMP1 ( date_time datetime);
SELECT * from #TEMP1
DECLARE @STARTTIME DATETIME = GETDATE();
DECLARE @SQLSTR NVARCHAR(MAX)='';SET @SQLSTR='insert into #TEMP1(date_time) values( cast(''' + cast(@STARTTIME as nvarchar(50)) + ''' AS DATETIME) )';
PRINT(@SQLSTR);
EXEC(@SQLSTR);
I would rather pass the value of @STARTTIME as a datetime parameter. It is one of the things you can do to help prevent SQL injection.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply