September 5, 2012 at 11:26 pm
Hi
I have this code below below and when I try to run run it I get this error:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"
please help.
DECLARE@StartDate VARCHAR(50), @EndDate VARCHAR(50), @userid VARCHAR(50)
SET @StartDate = '01-07-2001'
SET @EndDate = '29-12-2013'
SET @userid = 'jamesm'
SELECT[POLICY_ID]
,ISNULL(VALUE,0)[VALUE]
,ISNULL(UNITS,0)[UNITS]
,TX , CASH_FLOW_SET , PRODUCT_CODE , REC_DATE, [DATE]
FROM[Selestia_BI_Staging_Source]..PR_CF_HST CF (NOLOCK)
WHEREcf.[REC_DATE]>= @StartDateANDcf.[REC_DATE] <= @EndDate
AND cf.Fund_Code= 'FV'
September 6, 2012 at 12:38 am
It is not advisable to use Varchar datatype to store Date & Time values. Kindly declare the variables as DateTime and test it...
September 6, 2012 at 12:51 am
pls try below code..
DECLARE @StartDate VARCHAR(50), @EndDate VARCHAR(50), @userid VARCHAR(50)
SET @StartDate = '2001-07-01'
SET @EndDate = '2013-12-29'
SET @userid = 'jamesm'
SELECT [POLICY_ID]
, ISNULL(VALUE,0) [VALUE]
, ISNULL(UNITS,0) [UNITS]
, TX , CASH_FLOW_SET , PRODUCT_CODE , REC_DATE, [DATE]
FROM [Selestia_BI_Staging_Source]..PR_CF_HST CF (NOLOCK)
WHERE cf.[REC_DATE] >= @StartDate AND cf.[REC_DATE] <= @EndDate
AND cf.Fund_Code = 'FV'
September 7, 2012 at 10:02 am
The point above about not using VARCHAR for date values is a good one. However, I recently had a situation where the application was not doing a good job of date formatting on the front-end and this was causing errors when the stored procedure tried to run. I had no control over the value I was getting so I elected to accept the "date" as a varchar value and then test it within the stored procedure. Also, I recommend using DATEDIFF for checking date ranges rather than inequality operators. Something like this:
CREATE PROCEDURE [dbo].[DateCheck]
@dStartDate VARCHAR(20)
,@dEndDate VARCHAR(20)
,@dRecDate VARCHAR(20)
,@ERROR VARCHAR(50) OUTPUT
AS
BEGIN
SET DATEFORMAT DMY --optional depending on default system settings
BEGIN TRY
SET @dStartDate = CAST(@dStartDate AS SMALLDATETIME)
END TRY
BEGIN CATCH
SET @ERROR = 'The start date you entered is INVALID.'
SELECT @ERROR AS BadStartDate
RETURN
END CATCH
BEGIN TRY
SET @dEndDate = CAST(@dEndDate AS SMALLDATETIME)
END TRY
BEGIN CATCH
SET @ERROR = 'The end date you entered is INVALID.'
SELECT @ERROR AS BadEndDate
RETURN
END CATCH
BEGIN TRY
SET @dRecDate = CAST(@dRecDate AS SMALLDATETIME)
END TRY
BEGIN CATCH
SET @ERROR = 'The rec date you entered is INVALID.'
SELECT @ERROR AS BadRecDate
RETURN
END CATCH
SELECT
@dStartDate AS GoodStartDate
,@dEndDate AS GoodEndDate
,@dRecDate AS GoodRecDate
WHERE
DATEDIFF(DAY,@dStartDate,@dRecDate) >= 0
AND DATEDIFF(DAY,@dRecDate,@dEndDate) >= 0
END
To run this procedure and get back the error message for invalid dates:
DECLARE @ERROR VARCHAR(50)
EXEC [dbo].[DateCheck] '01-07-2001','29-12-2013','24-11-2012', @ERROR = @ERROR OUTPUT-- Valid dates
EXEC [dbo].[DateCheck] '01-07-2001','29-12-2013','24-11-1999', @ERROR = @ERROR OUTPUT-- Returns NULL (RecDate earlier than start date)
EXEC [dbo].[DateCheck] '01-07-2001','32-12-2013','24-11-2012', @ERROR = @ERROR OUTPUT-- ERROR: End date out of range
EXEC [dbo].[DateCheck] 'xyz','29-12-2013','24-11-2012', @ERROR = @ERROR OUTPUT-- ERROR: Invalid start date
September 7, 2012 at 10:12 am
I'd also like to see the DDL (CREATE TABLE statement) for the table PR_CF_HST.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply