In SQL Server 2012 is there a way to validate a string to a specific DateTime format MM/dd/yyyy-CodePudding

I have a very specific requirement in SQL Server 2012 where I need to match a given string to the specific date format of 'MM/dd/yyyy'. I do not need to CONVERT or CAST the string to a date, just a simple 1 or 0. Is the string in that format or not.

Example:

| ID |    Date    | Result |
|----|------------|--------|
| 1  | 05/31/2022 | 1      |
| 2  | 31/05/2022 | 1      |
| 3  | 2022/05/31 | 0      |
| 4  | 5/31/2022  | 0      |
| 5  | 5/31/22    | 0      |
| 6  | qwerty     | 0      |
| 7  | 5.31.2022  | 0      |
| 8  | 5-31-2022  | 0      |
| 9  | May/31/2022| 0      |

I had assumed a simple regex would work but I have not been able to find one that works in SQL. Currently I attempting to use '^[0-9]{2}/[0-9]{2}/[0-9]{4}' but this is failing for all values. I feel like I am missing something very simple but I cannot figure out what it is, and regex is not my strong suit as well.

Based on the comments i need to clarify something. The validation that is being done is 2 fold.

First, is the string a valid date, I have that covered no problem so I did not think to add that to this question originally.

Second, is the string in the appropriate format "2 digits '/' 2 digits '/' 4 digits". This second step is where I am requiring assistance.

Any guidance would be appreciated.

CodePudding user response：

This will check for valid dates in the format MM/dd/yyyy or dd/MM/yyyy, which basically satisfies your 2dig/2dig/4dig requirement while still checking for valid dates:

DECLARE @t TABLE (Dt VARCHAR(100));
INSERT @t
VALUES ('05/31/2022'),
       ('31/05/2022'),
       ('2022/05/31'),
       ('5/31/2022'),
       ('5/31/22'),
       ('qwerty'),
       ('5.31.2022'),
       ('5-31-2022'),
       ('May/31/2022'),
       ('19/99/2022');

SELECT Dt,
       IIF((TRY_CONVERT(DATE, Dt, 101) IS NOT NULL OR TRY_CONVERT(DATE, Dt, 103) IS NOT NULL)
       AND Dt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]',
           1,
           0)
FROM @t;

CodePudding user response：

With the updated question, it's pretty simple because there is one pattern. If more than one pattern was required, each would need to be tested.

DECLARE @testdata as TABLE(ID int, Date varchar(20), Result int)

INSERT @testdata VALUES
(1, '05/31/2022', 1),
(2, '31/05/2022', 1),
(3, '2022/05/31', 0),
(4, '5/31/2022', 0),
(5, '5/31/22', 0),
(6, 'qwerty', 0),
(7, '5.31.2022', 0),
(8, '5-31-2022', 0),
(9, 'May/31/2022', 0)

SELECT *, PATINDEX('[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]', Date) as [Calc] from @testdata

CodePudding user response：

I'm adding a second answer just to show what can also be added based purely on pattern matching. It tests the month and day values. It also allows for single or double digit month or day. I include both a basic 31 max day test on all months plus a test by month. The basic 31 day limit test is redundant if you want to test by month.

I don't test for a max 28 days for non-leap years. (It can be done, but logic is required to test the modulus when dividing the year by 4, 100, and 400.)

[EDIT] I attempted the leap year logic. Seems to work for a simple test.

This is not the original request, but it seems some might be interested in a more complete solution.

DECLARE @testdata as TABLE(ID int, Date varchar(20))

INSERT @testdata VALUES
(1, '05/31/2022'),
(2, '31/05/2022'),
(3, '2022/05/31'),
(4, '5/31/2022'),
(5, '5/31/22'),
(6, 'qwerty'),
(7, '5.31.2022'),
(8, '5-31-2022'),
(9, 'May/31/2022'),
(10, '1//2022'),
(11, '123/123/2022'),
(12, '123/123/123/2022'),
(13, '5/10/2022'),
(14, '2/30/2022'),
(15, '2/29/2000'),
(16, '2/29/2001'),
(17, '2/29/2004'),
(18, '2/29/2100')

SELECT *, 
    CASE WHEN PATINDEX('%[^0-9/]%', Date) = 0 -- only digits and /
        AND PATINDEX('%/%/%/%', Date) = 0 -- no more than 2 /
        AND PATINDEX('[0-9]%/[0-9]%/[0-9][0-9][0-9][0-9]', Date) > 0 -- 1 or more digit month and day with 4 digit year
        AND PARSENAME(REPLACE(Date, '/', '.'), 3) BETWEEN 1 AND 12 -- months are 1 to 12
        AND PARSENAME(REPLACE(Date, '/', '.'), 2) BETWEEN 1 AND 31 -- days are 1 to 31
        AND PARSENAME(REPLACE(Date, '/', '.'), 1) BETWEEN 1900 AND 3000 -- year limits?
        AND PARSENAME(REPLACE(Date, '/', '.'), 2) BETWEEN 1 AND ( -- max days by month ignoring leap years
            CASE PARSENAME(REPLACE(Date, '/', '.'), 3) WHEN 2 THEN 29 WHEN 4 THEN 30 WHEN 6 THEN 30 WHEN 9 THEN 30 WHEN 11 THEN 30 ELSE 31 END)
        AND (CASE WHEN PARSENAME(REPLACE(Date, '/', '.'), 3) <> 2 THEN 1 -- not feb, set to 1 to insure it passes
                WHEN PARSENAME(REPLACE(Date, '/', '.'), 1) % 4 = 0 
                    AND (PARSENAME(REPLACE(Date, '/', '.'), 1) % 100 <> 0 OR PARSENAME(REPLACE(Date, '/', '.'), 1) % 400 = 0) THEN 1 -- a leap year
                ELSE PARSENAME(REPLACE(Date, '/', '.'), 2) END -- use the feb day in the non-leap year
                ) <= 28 -- 28 is the max for non-leap years
        THEN 1
        ELSE 0 END as [Result]
FROM @testdata

ID          Date                 Result
----------- -------------------- -----------
1           05/31/2022           1
2           31/05/2022           0
3           2022/05/31           0
4           5/31/2022            1
5           5/31/22              0
6           qwerty               0
7           5.31.2022            0
8           5-31-2022            0
9           May/31/2022          0
10          1//2022              0
11          123/123/2022         0
12          123/123/123/2022     0
13          5/10/2022            1
14          2/30/2022            0
15          2/29/2000            1
16          2/29/2001            0
17          2/29/2004            1
18          2/29/2100            0