how can i use regular expressions to ensure a mysql format date entry in a text field?

[libsfan01]

how can i use regular expressions to ensure a mysql format date entry
in a text field?

thanks

marc

 

[RobG]

how can i use regular expressions to ensure a mysql format date entry
in a text field?

What is a "mysql format date entry"?

 

[libsfan01]

date in this format rob:

2006-03-02

regards

marc

 

[Bart Van der Donck]

how can i use regular expressions to ensure a mysql format date entry
in a text field?

I suppose you don't need years Ante Christum Natum or above 9999, which
aren't supported by MySQL's DATE type field. Please note that one- or
two-digit year notations are treated specially in MySQL
(0-69=>2000-2069 and 70-99=>1970-1999. Just use four digits to play
safe, eg 0006=>0006).

Note that the DATE field type in MySQL accepts Y(Y)(Y)(Y)-M(M)-D(D),
but doesn't actually check whether the date really exists up to day 31
(e.g. dates like 2006-2-31 are accepted).

Strictly the hyphens aren't even necessary, but I would counsel to keep
them in order to avoid all kinds of ambiguous situations.

Once you're aware of the above:

var D = '1941-8-19';
var S = D.split('-');

if ( /^\d{1,4}$/.test(S[0])
&& /^\d{1,2}$/.test(S[1])
&& 0 < parseFloat(S[1])
&& parseFloat(S[1]) < 13
&& /^\d{1,2}$/.test(S[2])
&& 0 < parseFloat(S[2])
&& parseFloat(S[2]) < 32
)
alert('date is OK to insert in MySQL');

Hope this helps,

 

[brattn]

doing it with regexp only and checking for Valid dates no 2006-2-29:

var CC = "((19)|(20))"; // valid centuries
// actually works for (1[7-9])|(2[0-3]) if you want to expand
century to 17-23
var Y29 = "(([02468][048])|([13579][26]))"; // years with 29 day in feb
var Y28 = "(([02468][1235679])|([13579][01345789]))"; // years with 28
days in feb
var M31 = "((0?[13578])|(1[02]))"; // months with 31 days
var M30 = "((0?[469])|(11))";// months with 30 days
var M2 = "(0?2)";// feb
var D31 = "((0?[1-9])|([12][0-9])|(3[01]))";// 31 days
var D30 = "((0?[1-9])|([12][0-9])|(30))";// 30 days
var D29 = "((0?[1-9])|([12][0-9]))"; // 29 days
var D28 = "((0?[1-9])|([12][0-8]))"; // 28 days
var sep = "-";// separator
var MonthsNotFeb = "(" + M31 + sep + D31 + ")|(" + M30 + sep+ D30 +
")";
var leapYears = Y29 + sep + "(" + MonthsNotFeb + "|(" + M2 + sep +
D29 + "))";
var nonleapYears = Y28 + sep + "(" + MonthsNotFeb + "|(" + M2 + sep +
D28 + "))";
var regDate = "^" + CC + "((" +leapYears + ")|(" + nonleapYears +
"))$";

// or the unreadable way:
var
regdate2='^((19)|(20))(((([02468][048])|([13579][26]))-((((0?[13578])|(1[02]))-((0?[1-9])|([12][0-9])|(3[01])))|(((0?[469])|(11))-((0?[1-9])|([12][0-9])|(30)))|((0?2)-((0?[1-9])|([12][0-9])))))|((([02468][1235679])|([13579][01345789]))-((((0?[13578])|(1[02]))-((0?[1-9])|([12][0-9])|(3[01])))|(((0?[469])|(11))-((0?[1-9])|([12][0-9])|(30)))|((0?2)-((0?[1-9])|([12][0-8]))))))$';

how can i use regular expressions to ensure a mysql format date entry
in a text field?

I suppose you don't need years Ante Christum Natum or above 9999, which
aren't supported by MySQL's DATE type field. Please note that one- or
two-digit year notations are treated specially in MySQL
(0-69=>2000-2069 and 70-99=>1970-1999. Just use four digits to play
safe, eg 0006=>0006).

Note that the DATE field type in MySQL accepts Y(Y)(Y)(Y)-M(M)-D(D),
but doesn't actually check whether the date really exists up to day 31
(e.g. dates like 2006-2-31 are accepted).

Strictly the hyphens aren't even necessary, but I would counsel to keep
them in order to avoid all kinds of ambiguous situations.

Once you're aware of the above:

var D = '1941-8-19';
var S = D.split('-');

if ( /^\d{1,4}$/.test(S[0])
&& /^\d{1,2}$/.test(S[1])
&& 0 < parseFloat(S[1])
&& parseFloat(S[1]) < 13
&& /^\d{1,2}$/.test(S[2])
&& 0 < parseFloat(S[2])
&& parseFloat(S[2]) < 32
)
alert('date is OK to insert in MySQL');

Hope this helps,

 

[RobG]

doing it with regexp only and checking for Valid dates no 2006-2-29:

Hmm... seems a bit over the top!

To the OP:

Just about everything you ever needed to know about date validation is
here:

<URL: http://www.merlyn.demon.co.uk/js-date4.htm >

The trivial way that checks the format only, not validity, is:

var dateString = '2006-2-31';
alert( /\d{4}-\d\d?-\d\d?/.test(dateString)); // Shows true


For validation, a simple way is to convert the incoming date string to
a date object then test if the generated date matches the string - you
only need to test 2 of the 3 bits:

var dateString = '2006-02-31';

alert( /\d{4}-\d{2}-\d{2}/.test(dateString));

var dateBits = dateString.split('-');
var dateObj = new Date(dateString.replace(/-/g,'/'));
var isValid = ( dateBits[0] == dateObj.getFullYear()
&& dateBits[1] == (dateObj.getMonth()+1));
alert(isValid); // Shows false.

 

[RobG]

doing it with regexp only and checking for Valid dates no 2006-2-29:

Hmm... seems a bit over the top!

To the OP:

Just about everything you ever needed to know about date validation is
here:

<URL: http://www.merlyn.demon.co.uk/js-date4.htm >

The trivial way that checks the format only, not validity, is:

var dateString = '2006-2-31';
alert( /\d{4}-\d\d?-\d\d?/.test(dateString)); // Shows true


For validation, a simple way is to convert the incoming date string to
a date object then test if the generated date matches the string - you
only need to test 2 of the 3 bits:

var dateString = '2006-02-31';

alert( /\d{4}-\d{2}-\d{2}/.test(dateString));

var dateBits = dateString.split('-');
var dateObj = new Date(dateString.replace(/-/g,'/'));
var isValid = ( dateBits[0] == dateObj.getFullYear()
&& dateBits[1] == (dateObj.getMonth()+1));
alert(isValid); // Shows false.

 

[Bart Van der Donck]

Just about everything you ever needed to know about date validation is
here:

<URL: http://www.merlyn.demon.co.uk/js-date4.htm >

The trivial way that checks the format only, not validity, is:

var dateString = '2006-2-31';
alert( /\d{4}-\d\d?-\d\d?/.test(dateString)); // Shows true

For validation, a simple way is to convert the incoming date string to
a date object then test if the generated date matches the string - you
only need to test 2 of the 3 bits:

var dateString = '2006-02-31';

alert( /\d{4}-\d{2}-\d{2}/.test(dateString));

var dateBits = dateString.split('-');
var dateObj = new Date(dateString.replace(/-/g,'/'));
var isValid = ( dateBits[0] == dateObj.getFullYear()
&& dateBits[1] == (dateObj.getMonth()+1));
alert(isValid); // Shows false.

While this is absolutely useful for actual date validation, it's
irrelevant to the OP's question in a strict sense, which was checking
dates that are flagged okay to insert in a MySQL DATE field (which is a
significanctly different requirement).

You'ld be surprised what kind of entries MySQL accepts:
22121 => 2022-12-01
100-05-21 => 2010-00-05
0 => 0000-00-00
etc.

Of course you have a point that it is in practice perhaps better to
just check for a valid date in the OP's case.

 

[Dr John Stockton]

JRS: In article <[email protected]>,
dated Wed, 11 Oct 2006 14:20:28 remote, seen in

For validation, a simple way is to convert the incoming date string to
a date object then test if the generated date matches the string - you
only need to test 2 of the 3 bits:

var dateString = '2006-02-31';

alert( /\d{4}-\d{2}-\d{2}/.test(dateString));

Matches '1234-56-789'.

var dateBits = dateString.split('-');
var dateObj = new Date(dateString.replace(/-/g,'/'));
var isValid = ( dateBits[0] == dateObj.getFullYear()
&& dateBits[1] == (dateObj.getMonth()+1));
alert(isValid); // Shows false.

If Y M D are arbitrary Numbers, then one need only test two of the three
parts. I'd not test the Year, but Month and Day in that order.

One can use .match instead of .test, saving a .split. Although .test
seems faster, .split seems slow (crudely tested).

If the Date is known to match \d{2} it is in 0..99, so any Date error
must give the wrong month (Date=365 generally gives the right month of the
wrong year); and a Month error also gives the wrong month. Therefore one
only needs to test the Month.

var dateString = '2006-02-31';

var M = dateString.match(/^(\d{4})-(\d{2})-(\d{2})$/)

alert(!!M)

var dateObj = new Date(dateString.replace(/-/g,'/'));
var isValid = M[2] == dateObj.getMonth()+1 // && M[3] == dateObj.getDate()
alert(isValid); // Shows false.