SQL Server date format MM/DD/YYYY

问题:

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

回答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, it's a valid date, but there's no guarantee that it's the date the user meant. If they enter:

06/07/2012

There is no way to know if they meant June 7 or July 6. Your best bet is to make users pick dates from drop-downs or calendar controls, which allows you to control the format and avoid any needless interpretation. Your application layer can use strongly typed variables / parameters and insert into properly typed columns.

回答2:

If you're after the SQL Server dateformat to see whether it's MDY then use:

dbcc useroptions

And have a look at the dateformat Set Option

回答3:

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

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

The output will be as of Sept 8th 2012

09/08/2012

There is no need to validate, other then checking the date field is null or not

回答4:

You have to do it outside the database. A database stores datetime internally in its own format. I dont think you can read what format the date is stored in. You can read it which ever way you like, for example 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 regular expression for that. But that will be outside the database.