CODEWITHSUNDEEP
sql
Daniel Bailey
Daniel Bailey

Reputation: 115

How to check date format on SQL Server

trying to check date format for all the values in my table and return a 1 if correct or 0 if false, here is my code so far, any suggestions would be appreciated. 

(Case when new_originationdate is not NULL
    AND new_originationdate not like '[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]'
        then 1 else 0 end) as CIEDIT_SYN_41,

Upvotes: 1

Views: 20176

Answers (2)

Md Masududzaman Khan
Md Masududzaman Khan

Reputation: 149

DBCC useroptions

Syntax The syntax goes like this:

DBCC USEROPTIONS
[ WITH NO_INFOMSGS ] The optional NO_INFOMSGS argument suppresses all informational messages that have severity levels from 0 through 10.

Example Here’s an example of running the command, and the results I get on my current test machine:

DBCC USEROPTIONS; Result:

+-------------------------+----------------+
| Set Option              | Value          |
|-------------------------+----------------|
| textsize                | -1             |
| language                | us_english     |
| dateformat              | mdy            |
| datefirst               | 7              |
| lock_timeout            | 5000           |
| quoted_identifier       | SET            |
| arithabort              | SET            |
| ansi_null_dflt_on       | SET            |
| ansi_warnings           | SET            |
| ansi_padding            | SET            |
| ansi_nulls              | SET            |
| concat_null_yields_null | SET            |
| isolation level         | read committed |

Upvotes: 1

John Wu
John Wu

Reputation: 52280

The problem with this approach is that it will treat "2017-13-32" as a valid date. It's not.

You didn't specify that platform you are running on. You can probably use a date/time function provided by your DB platform (see this link for SQL Server) to cast the string to an actual DateTime value. If the cast succeeds, the string is a valid date.

Upvotes: 1

Related Questions