January 31, 2019 at 4:46 am
Hi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:
[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
END
January 31, 2019 at 5:17 am
Just a thought - Are you sure there are no leading spaces in your date - because that will concatenate the string and give an error
Thanks
January 31, 2019 at 5:31 am
Lorna-331036 - Thursday, January 31, 2019 4:46 AMHi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
END
It's possibly related to the date format on your server rather than your input.
Try addingSET DATEFORMAT DMY
to your query. If you force it to use a particular date format it should solve the problem. Although taking a date input rather than a varchar is probably the way to go.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 31, 2019 at 5:53 am
Neil Burton - Thursday, January 31, 2019 5:31 AMAlthough taking a date input rather than a varchar is probably the way to go.
Or, if you must use a varchar (for reasons unknown) use a non-ambiguous format such as yyyyMMdd (i.e. 20190131) or yyyy-MM-ddThh:mm:ss (i.e. 2019-01-31T12:52:19).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2019 at 6:31 am
Lorna-331036 - Thursday, January 31, 2019 4:46 AMHi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
END
Validate the date in the client (web form). This goes for any kind of data entry. Your data should be correct BEFORE it meets the database, not after.
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
January 31, 2019 at 6:56 am
Thanks guys - I already validate before passing the string. I also added SET DATEFORMAT DMY.
The value, once validated, is passed to the DefaultValule of a SelectParameter in a GridView (.net webforms) - I have tested inputs and it's storing as "24/01/2019", but when the sproc is being called, it's still coming up with the same original error. If I can't resolve this, I'll have to do it the long winded way and store as DbType="Date" in the GridView rather than string. As the input could be a customer name OR date, I'll have to remove and add parameters in the GridView depending on what the user inputs.
January 31, 2019 at 7:03 am
Lorna-331036 - Thursday, January 31, 2019 6:56 AMThanks guys - I already validate before passing the string. I also added SET DATEFORMAT DMY.
The value, once validated, is passed to the DefaultValule of a SelectParameter in a GridView (.net webforms) - I have tested inputs and it's storing as "24/01/2019", but when the sproc is being called, it's still coming up with the same original error. If I can't resolve this, I'll have to do it the long winded way and store as DbType="Date" in the GridView rather than string. As the input could be a customer name OR date, I'll have to remove and add parameters in the GridView depending on what the user inputs.
Try setting the dateformat to YMD.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 31, 2019 at 7:04 am
Lorna-331036 - Thursday, January 31, 2019 6:56 AMThanks guys - I already validate before passing the string. I also added SET DATEFORMAT DMY.
The value, once validated, is passed to the DefaultValule of a SelectParameter in a GridView (.net webforms) - I have tested inputs and it's storing as "24/01/2019", but when the sproc is being called, it's still coming up with the same original error. If I can't resolve this, I'll have to do it the long winded way and store as DbType="Date" in the GridView rather than string. As the input could be a customer name OR date, I'll have to remove and add parameters in the GridView depending on what the user inputs.
CONVERT(Date, @searchTerm,103) will always convert valid dates in the format you've shown us - dd/mm/yyyy. If you're still getting errors then there's a wrong assumption somewhere. In your shoes, I'd add a temporary chunk of code to your sproc to save the parameter to a table as either a string or a sqlvariant, then investigate.
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
January 31, 2019 at 7:12 am
If you are still getting errors, then, honestly, I'd be concerned with using CONVERT at all. Clearly you aren't sure what format the dates are being passed or they're being passed in what ever format the user(?) types it in. That means that you could have someone typing the value "02/01/2018" and without asking the user, you have no idea if they mean 01 February 2018 or 02 January 2018. Maybe it's even worse and they type 10/11/12. Is that 10 November 2012, 11 October 2012, 12 November 2010 or maybe even 11 December 2010?
Like has been mentioned, I think at this stage you need to go down the route of ensuring that you have your application supply a proper date(time) datatype. There there is no ambiguity and your data engine doesn't need to "guess" the value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2019 at 7:41 am
Lorna-331036 - Thursday, January 31, 2019 4:46 AMHi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
END
If this code is failing, check the data!
😎
Strongly suggest that you put some input validation on the web form!
January 31, 2019 at 7:45 am
Eirikur Eiriksson - Thursday, January 31, 2019 7:41 AMLorna-331036 - Thursday, January 31, 2019 4:46 AMHi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
ENDIf this code is failing, check the data!
😎Strongly suggest that you put some input validation on the web form!
Great advice there buddy.. The DB needs to enforce the rules but you always need to validate at the source.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
January 31, 2019 at 7:49 am
--
-- Convert dd/mm/yyyy to a datetime format independend of regional and language settings.
--
declare @datestring varchar(200) = '24/01/2019'
select substring(@datestring,7,4)+substring(@datestring,4,2)+substring(@datestring,1,2)
select CONVERT(datetime,substring(@datestring,7,4)+substring(@datestring,4,2)+substring(@datestring,1,2))
When sure that the given format is 'dd/mm/yyyy'.
And you do not want regional and/or language settings 'to be involved',
Convert the string to 'yyyymmdd' and use the CONVERT function to obtain a datetime.
This should work in any SQL-server installation.
Ben
January 31, 2019 at 8:04 am
ChrisM@Work - Thursday, January 31, 2019 7:04 AMLorna-331036 - Thursday, January 31, 2019 6:56 AMThanks guys - I already validate before passing the string. I also added SET DATEFORMAT DMY.
The value, once validated, is passed to the DefaultValule of a SelectParameter in a GridView (.net webforms) - I have tested inputs and it's storing as "24/01/2019", but when the sproc is being called, it's still coming up with the same original error. If I can't resolve this, I'll have to do it the long winded way and store as DbType="Date" in the GridView rather than string. As the input could be a customer name OR date, I'll have to remove and add parameters in the GridView depending on what the user inputs.CONVERT(Date, @searchTerm,103) will always convert valid dates in the format you've shown us - dd/mm/yyyy. If you're still getting errors then there's a wrong assumption somewhere. In your shoes, I'd add a temporary chunk of code to your sproc to save the parameter to a table as either a string or a sqlvariant, then investigate.
You might be on the right track. I would ask what data type is Delivery_Date column? If it's not a date/time type it might be causing the error.
January 31, 2019 at 2:41 pm
Lorna-331036 - Thursday, January 31, 2019 4:46 AMHi
A user enters a date in uk format (i.e '24/01/2019') into a web form (.net). I then call my stored procedure to search records with a date field containing this date. Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string. Here is my stored procedure:[spSalesOrdersSearchDate]
(
@searchTerm varchar(10)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;SELECT *
FROM vSales
WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
AND Delivery_Date = CONVERT(Date, @searchTerm,103)
ORDER BY Company_Name,Order_Ref_No
END
I would rewrite your function to make it look more like SQL and less like COBOL or VB. The whole idea of the SQLs we gave you a language that has a tiered architecture. Your parameters should be formatted and cleaned up in the tier that invokes the procedure. This is not COBOL anymore. Everything was monolithic in that language.
Unlike basic, we don't have to put metadata on the names of procedures. In fact, putting metadata information is a design flaw so common and so bad it's called a Tibble.
We have a date data type, so there's no need convert strings with the old Sybase convert function. The in() predicate looks like SQL instead of a string of ORs, like Basic. When you port your code to another SQL product, many of them have optimizations for this predicate. In ANSI/ISO standard SQL PSM handles predicates a little differently than the old Sybase implementation. Parameters are explicitly labeled for in out or both, so I found it mimicking that helps readability.
The usual form for a procedure name is "<verb>_<object>", with a limited list of available verbs. The procedure does something, so it is a verb. Next, don't ever use SELECT * in production code. Finally, don't use order by in a procedure. The procedure should return a table and tables are not sorted by definition. Formatting and sorting your output data is done in a presentation layer.
CREATE PROCEDURE Search_SalesOrders_by_Date
(@in_search_date DATE)
AS
BEGIN
SET NOCOUNT ON;
SELECT company_name, order_ref_nbr, order_status
FROM Sales
WHERE order_status IN ('0', '4', '5')
AND delivery_date = @in_search_date;
END;
Having gone through all of this basic lecture on how to write SQL, I'm going to make a guess that you've got some weird stuff in your streams and that the conversion that you shouldn't be doing anyway is messing up.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 1, 2019 at 7:16 am
Lorna-331036 - Thursday, January 31, 2019 6:56 AMThanks guys - I already validate before passing the string. I also added SET DATEFORMAT DMY.
The value, once validated, is passed to the DefaultValule of a SelectParameter in a GridView (.net webforms) - I have tested inputs and it's storing as "24/01/2019", but when the sproc is being called, it's still coming up with the same original error. If I can't resolve this, I'll have to do it the long winded way and store as DbType="Date" in the GridView rather than string. As the input could be a customer name OR date, I'll have to remove and add parameters in the GridView depending on what the user inputs.
Use two parameters for your stored procedure; one of type "date" and the other "varchar". Populate the relevant one depending on the value from your GridView. This will give you a stored procedure with a where clause something like this;WHERE Delivery_Date = @SearchDate OR Customer_Name = @SearchName
This will get rid of your current problem, but give you another commonly known as "parameter sniffing". You can get around that by having two similar stored procedures; one with a date parameter and the other with a varchar parameter. You would call the appropriate one depending on what has been provided by the GridView.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply