February 1, 2006 at 4:43 pm
Why do I get this error when I have already set the format at the beginning of my stored proc?
Server: Msg 8114, Level 16, State 4, Procedure USP_RptActions, Line 0
Error converting data type varchar to datetime.
Below is my Stored Proc. Basically, an example of the @StartDate value could be '01/01/2006' and the @EndDate Value could be '25/01/2006'
It appears that the SET DATEFORMAT DMY is not happening!
CREATE PROC USP_RptActions @StartDate DATETIME, @EndDate DATETIME
AS
SET DATEFORMAT DMY
SELECT *
FROM ##Action
WHERE ActionStartDate >= @StartDate AND ActionEndDate <= @EndDate + '23:59:00.000'
February 1, 2006 at 4:47 pm
The error is not due to DATEFORMAT.
What are you expecting this to do ?
@EndDate + '23:59:00.000'
This won't perform a string concatenation, if that's your intention. The "Error converting data type varchar to datetime" is due to SQL attempting to convert '23:59:00.000' to add it to your enddate.
February 1, 2006 at 5:00 pm
February 1, 2006 at 5:03 pm
And you convert you parameters to DATETIME out of SP scope because parameters are datetime datatype.There is no point for SET DATEFORMAT DMY inside this SP.
_____________
Code for TallyGenerator
February 1, 2006 at 5:26 pm
Sergiy,
What do you mean "And you convert you parameters to DATETIME out of SP scope because parameters are datetime datatype.There is no point for SET DATEFORMAT DMY inside this SP. "
Are you syaing that because the user is passing the Date value as DD/MM/YY that I need to convert that to YYYY-MM-DD as it is stored in the Database?
In addition, I did what you said with the CONVERT and the same error appears!
February 1, 2006 at 5:49 pm
Datetime values are not stored in database as YYYY-MM-DD. They are stored as decimal numbers where int part represents number of full days passes since 1/1/1900 00:00:00.000 and fractional part represents part of the day passed.
It's good to read at least BOL about datatypes you are using.
If you define parameters of SP as DATETIME ideally you must supply values exactly in the format specified by type definition.
If you do not then you must supply values to allow implicit conversion to desired type.
Here you supply VARCHARs and want server to get it as DATETIME. It will do it, but in the way it was instructed to do by server settings. If your server is set as European you must talk to it in European language, if it's Japan - make it readable for Japan.
_____________
Code for TallyGenerator
February 1, 2006 at 6:10 pm
February 1, 2006 at 6:43 pm
Two ways to do it.
1. Check what are the settings of your SQL Server and convert strings from your forms into appropriate format inside your code, before you call SP.
2. Change parameter type to nvarchar. It will allow you to parse supplied values and convert it to datetime inside SP.
In both ways you must prepare varchar value for conversion to datetime. The only difference where you gonna do it - in application code or in SP.
_____________
Code for TallyGenerator
February 1, 2006 at 6:54 pm
February 1, 2006 at 7:07 pm
Something like this:
CREATE PROC USP_RptActions @StartDateString nvarchar(20), @EndDateString DATETIME
AS
DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = dbo.SomeConversionUDF(@StartDateString), @EndDate = dbo.SomeConversionUDF(@EndDateString)
SELECT ...
_____________
Code for TallyGenerator
February 1, 2006 at 7:13 pm
I think you may have made a typo on the @EndDateString!
Here is what I have tried but it doesn't seem to like to CONVERT the @EndDate!
DECLARE @StartDate NVARCHAR(20), @EndDate NVARCHAR(20)
SET @StartDate = '01/01/2006'
SET @EndDate = '25/01/2006'
DECLARE @StartDate1 DATETIME
DECLARE @EndDate1 DATETIME
SET @StartDate1 = CONVERT(DATETIME, @StartDate)
SET @EndDate1 = CONVERT(DATETIME, @EndDate)
SELECT @StartDate1, @EndDate1
I get an error of;
Server: Msg 8115, Level 16, State 2, Line 10
Arithmetic overflow error converting expression to data type datetime.
------------------------------------------------------ ------------------------------------------------------
2006-01-01 00:00:00.000 NULL
February 1, 2006 at 7:31 pm
Try this:
SET @StartDate = '05/01/2006'
SET @EndDate = '25/01/2006'
And I prescribed to use a function because within function you can check for ISDATE(@string) = 1, if not try another format, etc.
If you want to use simple CONVERT you need to specify style:
SET @EndDate1 = CONVERT(DATETIME, @EndDate, ....)
See BOL for details.
_____________
Code for TallyGenerator
February 1, 2006 at 7:40 pm
February 1, 2006 at 7:56 pm
You need to be sure nobody will change settings of front end application and sent you strings with dates in American format. Otherwise you SP will fail again.
_____________
Code for TallyGenerator
February 1, 2006 at 8:30 pm
The front end application doesn't use any date settings it just passes the 2 variables as strings and now the stored procedure does the remaining conversion to DATETIME Datatypes.
On the other hand, how come I have to use the style 103 to make it work? Why could I not just convert to DATETIME without the Style?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply