June 21, 2016 at 3:40 am
hello,
i have have a procedure that receives a date and a name and takes data from a table, but when i tried to add the date i get an error.
this is a sample table
create table aman
(
RequestDate datetime not null,
IP nvarchar (20),
RequestID float,
RequestType int,
CustomerID float,
LoanID float,
Ranking nvarchar (10),
)
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('10/10/2016', '10.10.10.22', 1234, 1, 2345, 4532, 'A');
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('10/11/2016', '10.10.10.22', 1244, 0, 2345, 4532, 'b');
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('06/21/2016', '10.10.10.22', 1234, 1, 2345, 4532, 'c');
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('10/10/2016', '10.10.10.22', 1234, 1, 2345, 4532, 'A');
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('10/11/2016', '10.10.10.22', 1244, 0, 2345, 4532, 'b');
insert into aman (RequestDate, IP, RequestID, RequestType, CustomerID, LoanID, Ranking)
values ('06/21/2016', '10.10.10.22', 1234, 1, 2345, 4532, 'c');
and the procedure
alter proc ShortReporRequest_sp
@CompanyName nvarchar(20),
@RequestDate datetime
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + 'where RequestDate >= ' + cast(@RequestDate as datetime)
+ 'and RequestType = 0'
exec sp_executesql @sqlquery
end
but when i tried to run it, i keep getting
Msg 241, Level 16, State 1, Procedure ShortReporRequest_sp, Line 40
Conversion failed when converting date and/or time from character string.
help pretty please :w00t::w00t::w00t:
June 21, 2016 at 3:51 am
Why do you have [font="Courier New"]cast(@RequestDate as datetime)[/font] when @RequestDate is already datetime? Do you not want to cast it to nvarchar(23) or something like that? Better still (much better still), parameterise your statement properly and this problem will go away. Read the documentation for sp_executesql if you're not sure how.
John
June 21, 2016 at 4:07 am
no, it was time, but i dont know what to do to get it working, so i am trying to cast here or there.
maybe i am calling the procedure wrong.
here is my call
exec ShortReporRequest_sp aman, '2016-06-21'
and i also tried calling it
exec ShortReporRequest_sp aman, '2016-06-21
and still not working, somewhere it is taking it as a string and not a date and i dont know where.
June 21, 2016 at 5:02 am
Well, it works for me if I convert to nvarchar(23) instead of datetime, and make a couple of other minor tweaks, thus:
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23))
+ ''' and RequestType = 0'
It also works if I parameterise properly, like this:
dec lare @TableName nvarchar(200), @sqlquery nvarchar(200), @params nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= @RequestDate and RequestType = 0'
set @params = '@RequestDate datetime'
exec sp_executesql @stmt = @sqlquery, @params = @params, @RequestDate = @RequestDate
John
June 21, 2016 at 5:12 am
astrid 69000 (6/21/2016)
no, it was time, but i dont know what to do to get it working, so i am trying to cast here or there.maybe i am calling the procedure wrong.
here is my call
exec ShortReporRequest_sp aman, '2016-06-21'
and i also tried calling it
exec ShortReporRequest_sp aman, '2016-06-21
and still not working, somewhere it is taking it as a string and not a date and i dont know where.
exec ShortReporRequest_sp 'aman', '2016-06-21'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 21, 2016 at 9:01 am
The problem is that you are trying to add a string to a date.
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + 'where RequestDate >= ' + cast(@RequestDate as datetime)
+ 'and RequestType = 0'
The strings 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + 'where RequestDate >= ' and 'and RequestType = 0' cannot be converted to dates, which is why you are getting the error.
You need it to be a string when constructing the dynamic SQL. Better yet, you want to parameterize your query as has already been suggested.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2016 at 4:34 am
Hi,
thanks, but it is still not using the date. it doesnt not give an error type anymore, but the outcome of the query is not what requested.
i do understand where the issue is, but i am having problems solving it. :w00t:
June 22, 2016 at 4:38 am
astrid 69000 (6/22/2016)
Hi,thanks, but it is still not using the date. it doesnt not give an error type anymore, but the outcome of the query is not what requested.
i do understand where the issue is, but i am having problems solving it. :w00t:
Some query recommendations have been offered to you and only you know which you may have decided to use. Can you post your new query so folks can see it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2016 at 5:01 am
hello
i tried both and neither of them helped.
they both fixed the issue of the data conversion, but neither gives back the correct date.
alter proc ShortReporRequest_sp
@CompanyName nvarchar(20),
@RequestDate datetime
as
begin
declare @TableName nvarchar(200)
declare @sqlquery nvarchar(200)
declare @params nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= @RequestDate and RequestType = 0'
set @params = '@RequestDate datetime'
exec sp_executesql @stmt = @sqlquery, @params = @params, @RequestDate = @RequestDate
end
-----
alter proc ShortReporRequest_sp
@CompanyName nvarchar(20),
@RequestDate datetime
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23))
+ ''' and RequestType = 1'
exec sp_executesql @sqlquery
end
June 22, 2016 at 5:13 am
Aren't you curious to see what your dynamically-constructed statement looks like?
alter proc ShortReporRequest_sp
@CompanyName nvarchar(20),
@RequestDate datetime
as
begin
declare @TableName nvarchar(200)
declare @sqlquery nvarchar(200)
declare @params nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= @RequestDate and RequestType = 0'
PRINT @sqlquery -- Check by eye that the statement is correctly formed, if necessary copy it, paste into a new window, and attempt to execute.
set @params = '@RequestDate datetime'
exec sp_executesql @stmt = @sqlquery, @params = @params, @RequestDate = @RequestDate
end
-----
alter proc ShortReporRequest_sp
@CompanyName nvarchar(20),
@RequestDate datetime
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(200)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'SELECT RequestDate, IP, RequestID, CustomerID, LoanID, Ranking FROM ' + @TableName + ' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23))
+ ''' and RequestType = 1'
PRINT @sqlquery -- Check by eye that the statement is correctly formed, if necessary copy it, paste into a new window, and attempt to execute.
exec sp_executesql @sqlquery
end
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2016 at 5:34 am
i know what my error is, i just don't know how to fix it. :w00t:
June 22, 2016 at 5:48 am
astrid 69000 (6/22/2016)
i know what my error is, i just don't know how to fix it. :w00t:
It's the opposite way around for us!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 7:02 pm
Your problem is here:
' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23)) + ''''
1. Never use CAST is SQL, especially for date-time related data types.
The output of CAST depends on many environmental settings which you sometimes cannot control and may be even not aware of.
Just forget the command even exists.
There is CONVERT for this purpose.
It allows you to apply a specific style to the output.
2. In your query the engine will need to implicitly convert the varchar(23) string you pass to it to the data type of RequestDate, which is - datetime, date, datetime2?
To enforce correct interpretation of the date supplied as a string you need to choose an ISO format which allows only one possible interpretation:
SELECT
QUOTENAME(CONVERT(VARCHAR(23), @RequestDate, 112), '''') -- For date only values
,QUOTENAME(CONVERT(VARCHAR(23), @RequestDate, 126), '''') -- For date and time values
So, your query should look like:
' where RequestDate >= ' + QUOTENAME(CONVERT(VARCHAR(23), @RequestDate, 126), '''')
_____________
Code for TallyGenerator
June 29, 2016 at 8:36 am
Sergiy (6/28/2016)
Your problem is here:
' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23)) + ''''1. Never use CAST is SQL, especially for date-time related data types.
I disagree. CAST is ANSI standard, whereas CONVERT is T-SQL specific. I always use CAST unless I need to deal with a specific format that is different from the ANSI standard formats (especially for dates).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2016 at 9:31 am
astrid 69000 (6/22/2016)
i know what my error is, i just don't know how to fix it. :w00t:
So what is the problem? We can't see what you see.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply