Dates and varchar

Neil 396 Reputation points
2022-01-31T15:55:47.913+00:00

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.
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

5 additional answers

Sort by: Most helpful
  1. Neil 396 Reputation points
    2022-02-01T02:19:56.52+00:00

    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!

    0 comments No comments

  2. ZoeHui-MSFT 32,401 Reputation points
    2022-02-01T07:01:50.877+00:00

    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.

    0 comments No comments

  3. Neil 396 Reputation points
    2022-02-01T12:35:28.88+00:00

    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
    
    0 comments No comments

  4. Olaf Helper 40,576 Reputation points
    2022-02-02T08:48:38.303+00:00

    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?