Dates and varchar

Question

For the below code I was just expecting it to provide plan varchar value 20312201 in-case of invalid date, but it is giving error
how to handle this?

I am trying to write a code(function) if is not converting to valid date after cleaning it should return the same value in @dateParam (with or without cleaning + - etc ). I have this in function and with if len(@dateparam) = 8 checking before entering this block

Msg 241, Level 16, State 1, Line 36
Conversion failed when converting date and/or time from character string.

DECLARE @date varchar(30)
    DECLARE @dateParam varchar(30) = '20312201'
    DECLARE @StartDt DATE ='1900-01-01'
    DECLARE @EndDt   DATE ='2099-01-01'

    SET @date = @dateParam
    SET @dateParam = Replace(Replace(Replace(@dateparam, '+', ''), '-',''),' ','')  
    PRINT @dateParam

    SET @date = SUBSTRING(@dateParam, 1, 4) + '-' + SUBSTRING(@dateParam, 5, 2) + '-' + SUBSTRING(@dateParam, 7,2)+' 00:00:00.000'
    SET @date = CASE WHEN ISDATE(@date) = 1 THEN @date 
                ELSE 
                    SUBSTRING(@dateParam, 5, 4) + '-' + SUBSTRING(@dateParam, 3, 2) + '-' + SUBSTRING(@dateParam, 1,2)+' 00:00:00.000'
                END
    SET @date = CASE WHEN (CAST(@date AS DATE) > @StartDt AND CAST(@date AS DATE) < @EndDt) THEN @date
                ELSE 
                    @dateParam
                END 
    SELECT @date, @dateParam 


--20312201
Msg 241, Level 16, State 1, Line 36
Conversion failed when converting date and/or time from character string.

Answer 1

Change
SET @apl = CASE WHEN (CAST(@apl AS DATE) > @StartDt AND CAST(@apl AS DATE) < @EndDt) THEN @apl
To
SET @apl = CASE WHEN (Try_Convert(DATE, @apl ) > @StartDt AND Try_Convert(DATE, @apl ) < @EndDt) THEN @apl

Tom

Answer 2

The above solution is working for date with length 8, I am working on developing the solution for date with varying length say 12,14,17 and 19 with time stamp.

Thanks much Tom!

Answer 3

Hi @Neil ,

If the length for @apl is stable like 12/14/17/19, you may try with below code.

set @dateParam=SUBSTRING(@dateParam,1,4) + '-' + SUBSTRING(@dateParam,5,2) + '-' + SUBSTRING(@dateParam,7,2) + ' ' + SUBSTRING(@dateParam,9,2) + ':' +   
SUBSTRING(@dateParam,11,2) + ':' + case when len(@dateParam)=12 then '00' else SUBSTRING(@dateParam,13,2) end    
+ '.' + case when len(@dateParam)<17 then '000' else SUBSTRING(@dateParam,15,3) end  
+ '.' + case when len(@dateParam)<19 then '00' else SUBSTRING(@dateParam,18,2) end  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi Zoe,

The length is not fixed, there are examples of lengths 4,6(which have to reject as invalid date), 8, 12, 14, 17, 19 in case it is valid I have to consider it by taking date and time if available, else reject all the invalid dates.

The original post is working for 8 length and I have written below for all other lengths

I have to reject all dates < 1900-01-01 and > 2099-01-01 along with all other invalid dates of length 4, 6 etc. Reject in the sense, just return the same value what is received instead of formatting it to a valid date, just return the same value without format. (it is fine if it is removing + - etc. with the replace statement below before returning the invalid data(dates))

FUNCTION [dbo].[CleanDate](@dateParam varchar(30))  
RETURNS varchar(30)   
AS   
BEGIN  
DECLARE @date VARCHAR(30);
DECLARE @StartDt DATE ='1900-01-01'
DECLARE @EndDt   DATE ='2099-01-01'
DECLARE @TestDt  DATE 

SET @date = @dateParam
SET @dateParam = Replace(Replace(Replace(@dateparam, '+', ''), '-',''),' ','')
IF LEN(@dateParam) >= 15 
BEGIN
SET @date = SUBSTRING(@dateParam, 1, 4) + '-' + SUBSTRING(@dateParam, 5, 2) + '-' + SUBSTRING(@dateParam, 7,2)
+' '+SUBSTRING(@dateParam, 9, 2) + ':'+SUBSTRING(@dateParam, 11, 2) + ':'+SUBSTRING(@dateParam, 13, 2) + '.'+SUBSTRING(@dateParam, 15, 3)
SET @date = CASE WHEN (Try_Convert(DATE, @date) > @StartDt AND Try_Convert(DATE, @date) < @EndDt) THEN @date
             ELSE @dateParam
 END 
END

ELSE IF LEN(@dateParam) = 14
BEGIN
SET @date = SUBSTRING(@dateParam, 1, 4) + '-' + SUBSTRING(@dateParam, 5, 2) + '-' + SUBSTRING(@dateParam, 7,2)
+' '+SUBSTRING(@dateParam, 9, 2) + ':'+SUBSTRING(@dateParam, 11, 2) + ':'+SUBSTRING(@dateParam, 13, 2)+'.000'
SET @date = CASE WHEN (Try_Convert(DATE, @date) > @StartDt AND Try_Convert(DATE, @date) < @EndDt) THEN @date
             ELSE @dateParam
 END 
END

ELSE IF LEN(@dateParam) = 12
BEGIN
SET @date = SUBSTRING(@dateParam, 1, 4) + '-' + SUBSTRING(@dateParam, 5, 2) + '-' + SUBSTRING(@dateParam, 7,2)
+' '+SUBSTRING(@dateParam, 9, 2) + ':'+SUBSTRING(@dateParam, 11, 2) +':00.000'
SET @date = CASE WHEN (Try_Convert(DATE, @date) > @StartDt AND Try_Convert(DATE, @date) < @EndDt) THEN @date
             ELSE @dateParam
 END 
END

ELSE IF LEN(@dateParam) = 8
BEGIN
SET @date = SUBSTRING(@dateParam, 1, 4) + '-' + SUBSTRING(@dateParam, 5, 2) + '-' + SUBSTRING(@dateParam, 7,2)+' 00:00:00.000'
SET @date = CASE WHEN ISDATE(@date) = 1 THEN @date 
ELSE 
SUBSTRING(@dateParam, 5, 4) + '-' + SUBSTRING(@dateParam, 3, 2) + '-' + SUBSTRING(@dateParam, 1,2)+' 00:00:00.000'
END
SET @date = CASE WHEN (Try_Convert(DATE, @date) > @StartDt AND Try_Convert(DATE, @date) < @EndDt) THEN @date
        ELSE 
@dateParam
END 
END
RETURN @date
END

Answer 5

it to provide plan varchar value 2031 22 01

If this is the common date format YYYYMMDD, then what for a month is 22; on my calendar I don't have such?