SQL 2005 stored procedure wont acccept UK date format
Posted on 2014-01-16
I have ASP.NET 2010 code which executes a stored procedure on SQL 2005. It passes some parameters to this procedure including two dates. In this code, I’m using the following CDate functions for the dates.
tempWorkflowStartDate = CDate(txtWorkflowStartDate.Text).ToString("yyyy-MM-dd")
tempSalesEffectiveFrom = CDate(txtSaleseffectiveFrom.Text).ToString("yyyy-MM-dd")
The output from the ASP.NET code can be run in a query window on SQL:
EXEC spSubmitNPRRequest 'Test', 'firstname.lastname@example.org', 'brief description', '14/01/2014', '31/01/2014'
The date format is and should be British. I’ve used a calander extender which accepts the British date format and displays it that way too.
When I execute the query, I get the following error: “Msg 8114, Level 16, State 5, Procedure spSubmitNPRRequest, Line 0 Error converting data type varchar to datetime.”
If I change the date formats manually on the query to look like US format it executes and works fine like so:
EXEC spSubmitNPRRequest 'Test', 'email@example.com', 'brief description', '01/14/2014', '01/31/2014'
When viewing the data table, the formats are back to how I expect (UK format). '14/01/2014', '31/01/2014'
Does anyone know how I can get the stored procedure to accept the UK date formats? I’ve tried Google and a lot of obvious formatting to get it to work but I’m not having any success.