How to validate date field with format MMM/DD/YY or MMM/DD/YYYY?

user_1696605 0
Viewed 1033

My field contains the string values like JUL/11/2017, JAN/11/17. Though it is a valid field, i am not able to validate it using is_date function

SET DATEFORMAT MDY;  
if isdate('JUL/11/2017')=1
print 'VALID_DATE'
else
print 'invalid date'

If the field value is DD/MMM/YY or DD/MMM/YYYY , it works fine. Any one can help me validating this field.

Note:I have tried using set language option too.

sql-server-2005 sql-server-2008 sql-server sql
4 Answers

What you need is just to replace your '/' separator with space to have 109 data format:

SET DATEFORMAT MDY;  
if isdate(replace('JUL/11/2017', '/', ' '))=1
print 'VALID_DATE'
else
print 'invalid date'
user_76104560

You know for sure that the following format are OK, right?

DD/MMM/YY 
DD/MMM/YYYY

Then, what can we do is to transform the MMM/DD/YY and the MMM/DD/YYYY formats like them, and the to check if the date is valid:

DECLARE @Date01 VARCHAR(12) = 'JAN/11/17';
DECLARE @Date02 VARCHAR(12) = 'JAN/11/2017';

DECLARE @NewDate VARCHAR(12);

DECLARE @xml XML;
SET @xml = CAST(N'<t>' + REPLACE(@Date01,'/','</t><t>') + '</t>' AS XML);

SELECT STUFF
(
    (
        SELECT '/' + c.value('.','varchar(MAX)') as item              
        FROM  @xml.nodes('/t') as T(c)
        ORDER BY CASE ROW_NUMBER() OVER(ORDER BY T.c) 
                 WHEN 1 THEN 2
                 WHEN 2 THEN 1
                 WHEN 3 THEN 3
                 END
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(12)')
    ,1
    ,1
    ,''
);
user_10803540

Try the following:

DECLARE @Date VARCHAR(20)='JUL/11/2017'

SELECT @Date=STUFF(STUFF(@Date,1,3,RIGHT(LEFT(@Date,6),2)),4,2,LEFT(@Date,3))--,LEFT(@Date,3),RIGHT(LEFT(@Date,6),2)

IF ISDATE(@Date)=1
print 'VALID_DATE'
else
print 'INVALID_DATE'
user_38765650

You can try the following too:

DECLARE @DATE_FIELD VARCHAR(15) = 'JUL/11/2017' --'JAN/11/17'
    IF (ISDATE(REVERSE(LEFT(REVERSE(@DATE_FIELD),CHARINDEX('/',REVERSE(@DATE_FIELD),1)-1))+'-'+ LEFT(@DATE_FIELD,CHARINDEX('/',@DATE_FIELD,1)-1)+'-'+SUBSTRING(@DATE_FIELD,CHARINDEX('/',@DATE_FIELD,1)+1,LEN(@DATE_FIELD)-CHARINDEX('/',@DATE_FIELD,1)-CHARINDEX('/',REVERSE(@DATE_FIELD),1))) = 1)
    PRINT 'VALID_DATE'
    ELSE
    PRINT 'INVALID_DATE'

Thanks.

user_39877840
Related