Displaying page
of
pages;
Items to
|
Title |
Test
Details
SQL Server 2005 datetime validation
|
|
Expression |
^((((((((jan(uary)?)|(mar(ch)?)|(may)|(july?)|(aug(ust)?)|(oct(ober)?)|(dec(ember)?)) ((3[01])|29))|(((apr(il)?)|(june?)|(sep(tember)?)|(nov(ember)?)) ((30)|(29)))|(((jan(uary)?)|(feb(ruary)?|(mar(ch)?)|(apr(il)?)|(may)|(june?)|(july?)|(aug(ust)?)|(sep(tember)?)|(oct(ober)?)|(nov(ember)?)|(dec(ember)?))) (2[0-8]|(1\d)|(0?[1-9])))),? )|(((((1[02])|(0?[13578]))[\.\-/]((3[01])|29))|(((11)|(0?[469]))[\.\-/]((30)|(29)))|(((1[0-2])|(0?[1-9]))[\.\-/](2[0-8]|(1\d)|(0?[1-9]))))[\.\-/])|(((((3[01])|29)[ \-\./]((jan(uary)?)|(mar(ch)?)|(may)|(july?)|(aug(ust)?)|(oct(ober)?)|(dec(ember)?)))|(((30)|(29))[ \.\-/]((apr(il)?)|(june?)|(sep(tember)?)|(nov(ember)?)))|((2[0-8]|(1\d)|(0?[1-9]))[ \.\-/]((jan(uary)?)|(feb(ruary)?|(mar(ch)?)|(apr(il)?)|(may)|(june?)|(july?)|(aug(ust)?)|(sep(tember)?)|(oct(ober)?)|(nov(ember)?)|(dec(ember)?)))))[ \-\./])|((((3[01])|29)((jan)|(mar)|(may)|(jul)|(aug)|(oct)|(dec)))|(((30)|(29))((apr)|(jun)|(sep)|(nov)))|((2[0-8]|(1\d)|(0[1-9]))((jan)|(feb)|(mar)|(apr)|(may)|(jun)|(jul)|(aug)|(sep)|(oct)|(nov)|(dec)))))(((175[3-9])|(17[6-9]\d)|(1[89]\d{2})|[2-9]\d{3})|\d{2}))|((((175[3-9])|(17[6-9]\d)|(1[89]\d{2})|[2-9]\d{3})|\d{2})((((1[02])|(0[13578]))((3[01])|29))|(((11)|(0[469]))((30)|(29)))|(((1[0-2])|(0[1-9]))(2[0-8]|(1\d)|(0[1-9])))))|(((29feb)|(29[ \.\-/]feb(ruary)?[ \.\-/])|(feb(ruary)? 29,? ?)|(0?2[\.\-/]29[\.\-/]))((((([2468][048])|([3579][26]))00)|(17((56)|([68][048])|([79][26])))|(((1[89])|([2-9]\d))(([2468][048])|([13579][26])|(0[48]))))|(([02468][048])|([13579][26]))))|(((((([2468][048])|([3579][26]))00)|(17((56)|([68][048])|([79][26])))|(((1[89])|([2-9]\d))(([2468][048])|([13579][26])|(0[48]))))|(([02468][048])|([13579][26])))(0229)))$
|
|
Description |
This expression validates most legal dates for a SQL Server 2005 datetime format. It includes checking for leap years. (Written for a default U.S. install, not sure what results would be on versions for different regions)
For double digit dates, 00 is considered to be the year 2000, so leap years will be valid.
This will handle dates in the following formats
yymmdd
yyyymmdd
m/d/yy
mm/dd/yy
m/d/yyyy
mm/dd/yyyy
m-d-yy
mm-dd-yy
m-d-yyyy
mm-dd-yyyy
m.d.yy
mm.dd.yy
m.d.yyyy
mm.dd.yyyy
mmmm dd yy
mmmm dd yyyy
mmmm dd, yy
mmmm dd, yyyy
mmmm d yy
mmmm d yyyy
mmmm d, yy
mmmm d, yyyy
d mmmm yy
d mmmm yyyy
dd mmmm yy
dd mmmm yyyy
d-mmmm-yy
d-mmmm-yyyy
dd-mmmm-yy
dd-mmmm-yyyy
d.mmmm.yy
d.mmmm.yyyy
dd.mmmm.yy
dd.mmmm.yyyy
d/mmmm/yy
d/mmmm/yyyy
dd/mmmm/yy
dd/mmmm/yyyy
mmm dd yy
mmm dd yyyy
mmm dd, yy
mmm dd, yyyy
mmm d yy
mmm d yyyy
mmm d, yy
mmm d, yyyy
dd mmm yy
dd mmm yyyy
d mmm yy
d mmm yyyy
ddmmmyy
ddmmmyyyy
dd-mmm-yy
dd-mmm-yyyy
d-mmm-yy
d-mmm-yyyy
dd.mmm.yy
dd.mmm.yyyy
d.mmm.yy
d.mmm.yyyy
dd/mmm/yy
dd/mmm/yyyy
d/mmm/yy
d/mmm/yyyy
Note: This will also handle mixing and matching of separator characters. for example:
'1/1.2000',
It would appear that this is valid in SQL Server 2005.
This is written to work for ECMA script (include the ignore case parameter i), but should also work in .Net.
I have tested this in firefox 2.0 (Blazing Fast) and IE 7.0 and both seem to work just fine. I have not tested it in .Net.
It's pretty large and I have not attempted to optimize it. Feel free to optimize it any way you like.
|
|
Matches |
20000229 | 000229 | 12/31/9999 | Dec 02, 99 | 30MAR1753 | Feb 29, 1756 | 1.1.2008
|
|
Non-Matches |
21000229 | 990229 | 12/32/9999 | Xxx 02, 99 | 30MAR1752 | Feb 29, 1752 | 1.0.2008
|
|
Author |
Rating:
Christopher Strolia-Davis
|
Displaying page
of
pages;
Items to
