Pisoi Flo
Pisoi Flo

Reputation: 75

Check if format is YYYY-MM-DD

I need to check if the format stored in an SQL table is YYYY-MM-DD.

Upvotes: 2

Views: 39145

Answers (7)

Abhishek Kukreja
Abhishek Kukreja

Reputation: 21

you can also do like this to check YYYY-MM-DD

SELECT * 
FROM your_table 
WHERE regexp_like(invoice_date,'^\d{4}-\d{2}-\d{2}$');

Explanation of the regular expression pattern:

  • ^ asserts the start of the string.
  • \d{4} matches exactly four digits (for the year).
  • matches the hyphen character literally.
  • \d{2} matches exactly two digits (for the month).
  • matches another hyphen character.
  • \d{2} matches exactly two digits (for the day).
  • $ asserts the end of the string.

Upvotes: 1

Joey Phillips
Joey Phillips

Reputation: 1625

This already has an accepted answer but I found a way to do this without regular expressions if anyone wants:

DECLARE @dateString VARCHAR(12) = '2023-05-12';

SELECT CASE WHEN TRY_CAST(@dateString AS DATE) IS NOT NULL AND @dateString = FORMAT(TRY_CAST(@dateString AS DATE), 'yyyy-MM-dd')
            THEN 'Valid'
            ELSE 'Invalid'
       END AS ValidationResult;

Hope it helps!

Upvotes: 0

Shanku
Shanku

Reputation: 1

try this to find format of values for date or varchar date field. Thanks

DECLARE  @Description VARCHAR(20)
SET @Description='200202'
SELECT CASE WHEN patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 101 
            WHEN patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 110
            WHEN patindex('%[0-9][0-9][0-9][0-9].[0-9][0-9].[0-9][0-9]%',@Description)=1 THEN 102 
            WHEN patindex('%[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',@Description)=1 THEN 111
            WHEN patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@Description)=1 THEN 112
            WHEN patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 103 
            WHEN patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 104
            WHEN patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 105
    ELSE 101 END 

Upvotes: 0

Pred
Pred

Reputation: 9042

First and foremost: If the date value is stored in date or datetime or equivalent temporal data type column there is no such thing as date format in the database level. The date itself was validated by the rules defined in the specific database engine's given data type and stored in whatever binary format it defines.

If the data is a STRING (from a file or a varchar column for example), then you can validate if it is in a given format using the TO_DATE() or TRY_CONVERT() functions in newer versions of SQL Server and STR_TO_DATE() in MySQL, or you can use 3rd party/self written modules/clrs to do it.

These validations will only check if the string maches the given format and the date parts are in the acceptable range of dates, but it will not test the meaning of the value. Converting the 02/03/2005 string to date is valid for the MM/DD/YYYY and DD/MM/YYYY format too, and there is no way to tell which one is the real value unless we have information about the environment originally stored it.

NEVER EVER store temporal data in character based column (like varchar), use the data type matches your needs (DATE/DATETIME/TIMESTAMP/whatever).

Upvotes: 2

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this way

SELECT CASE WHEN ISDATE(@string) = 1 
  AND @string LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' 
  THEN 1 ELSE 0 END;

@string is date.

Upvotes: 3

Sree Hari
Sree Hari

Reputation: 24

You can set the format but as far as I know you cant check the format.

Upvotes: 0

Adnan Isajbegovic
Adnan Isajbegovic

Reputation: 2307

You dont store specific format (i think ms sql stores it as two integers), you select your format for output. And when I say you select, I mean you have your default (mostly set automatically when installing MS SQL or whatever you use based on your country, timezone, etc - you can change this) and those which you choose to when executing scripts.

Upvotes: 3

Related Questions