SQL Server Date Format MM / DD / YYYY

Question

How to check if a date string is in MM / DD / YYYY format in SQL Server?

Answer 1

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

If the result is 1, this is a valid date, but there is no guarantee that this is the date that the user had. If they enter:

 06/07/2012 

It is not possible to find out whether the place took place on June 7 or July 6. It’s best to have users select dates from drop-down lists or calendar controls, which allows you to control the format and avoid unnecessary interpretation. Your application layer can use strongly typed variables / parameters and insert into correctly typed columns.

Answer 2

If after the SQL Server date format you find out if it uses MDY:

 dbcc useroptions 

And look at the dateformat Set Option

Answer 3

you convert date to datestring in this format MM / DD / YYYY using the CONVERT function

 select convert(varchar(10),getdate(),101) 

Exit will be from September 8, 2012.

 09/08/2012 

No need to check, moreover, checking the date field is zero or not

Answer 4

You must do this outside the database. The database stores the date and time internally in its own format. I don’t think you can read in what format the date is stored. You can read it that you ever like, e.g. dd / mm / yyyy or yyyy / mm / dd etc.

What you can do is check this value outside the database for any date field. You can use a regular expression for this. But it will be outside the database.