Date parameter in SQL Server stored procedure from ASP.NET

Question

I've written an SQL Server stored procedure that, amongst other things, accepts a date parameter:

CREATE PROCEDURE data.addScore
    ... (some parameters),
    @testDate date
AS

On an ASP.NET page I have a textbox with a Calender Extender control (from the AJAX Toolkit) that allows the user to select a date; the date is stored in the textbox in the format DD/MM/YYYY. I have some code that is supposed to execute the stored procedure with the parameters, using the date value from the textbox:

cmd.Parameters["@testDate"].Value = Convert.ToDateTime(((TextBox)li.FindControl("date")).Text);

I know I can definitely get to the textbox's value using ((TextBox)li.FindControl("date")).Text, but for the life of me I can't get it into a format that doesn't throw up one of a few errors, mainly 'Input string was not in the correct format'.

When I run the stored procedure directly on the server I usually bind the parameter like @testDate = 'YYYYMMDD'.

Aaron Bertrand · Accepted Answer · 2013-09-09 04:29:49Z

3

I would suggest you don't store anything in the textbox using regional, ambiguous formats like dd/mm/yyyy. If you use an unambiguous format like YYYYMMDD, you're not going to have any issues. And if users are picking from a calendar control or something, it should be easy to separate the date you present to them from the date you pass to the database.

answered Sep 9, 2013 at 4:29

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Katstevens Over a year ago

Point taken - thanks for your answer. I'd like to understand it all though - at present I have an SqlDataSource using the same textbox as a ControlParameter to a date field and it works perfectly. What am I doing wrong in my code behind that ASP.NET does correctly for it's own binding?

Aaron Bertrand Over a year ago

@Katstevens I don't know your whole setup, but it's likely that some layer is using US English for locale, or dateformat of m/d/y. The safest approach is to just always use unambiguous formats when passing string literals to the database, regardless of what you show users (though I suggest unambiguous formats there too).

Katstevens Over a year ago

At present I am using Convert.ToDateTime to get a DateTime object holding the date, and it is parsing it correctly. I thought passing this in to the database parameter would be the most unambiguous solution as it is not reliant on formatting. I get an error about converting a string to Int32 though - but I am using a date object! Thoughts?

senthilkumar2185 · Accepted Answer · 2013-09-09 06:01:54Z

0

<asp:Textbox id="date" runat="Server"></asp:Textbox>

<cc1:CalendarExtender ID="calwhendate" runat="server" Enabled="true" Format="YYYYMMDD"
TargetControlID="date"></cc1:CalendarExtender>

Change your Ajax Calendar Control to your desired format

answered Sep 9, 2013 at 6:01

senthilkumar2185

2,5983 gold badges23 silver badges38 bronze badges

Collectives™ on Stack Overflow

Date parameter in SQL Server stored procedure from ASP.NET

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related