Re: Date field in DD/MM/YYY

Chris Roy-Smith wrote:
> Jes wrote:
>> I have a date field on a HTML form where the user is asked to key in
>> dd/mm/yyyy

Bad idea.

>> However, when that is written to MySql it is either not
>> accepted or another value is tored in the database.
>>
>> Is there any way to change value of this field back to yyyymmdd format
>> as accepted correctly in sql. ? The change should preferably be when
>> user click on submit.
>> Does anyone know of any code/function that does this ?
>>
>> I'm using PHP and HTML

Should be done server-side as already suggested.

>> [...]
>
> Hi Jesmond,
> I use the following functions for this task in php. This saves the user from
> seeing "strange dates". Feel free to modify to suit your needs. I wrote
> this code while starting out in php so it's not a slick as it could be,

Indeed it isn't. However, if the form was designed better, the conversion
was not necessary.

> but it does the job, and I havn't got around to tidying it up. (If it aint
> broke.......)
>
> > //this function fort making sql dates readable to non-logical date users...
>
> function toAusDate($SQLDate){
> if ($SQLDate==null){
> return null;
> }
> $DateArray=explode("-", $SQLDate);
> if ($year=="0000"){
> return "Unknown";
> }else{
> $val = $date[2].'-'.$date[1].'-'.$date[0];
> return $val;
> }
> }
>
> // same as above, but handles time as well
>
> function ToAusDateTime($SQLDateTime){
> $year=substr($SQLDateTime,0,4);
> $month=substr($SQLDateTime,5,2);
> $day=substr($SQLDateTime,8,2);
> $hour=substr($SQLDateTime,11,2);
> $min=substr($SQLDateTime,14,2);
> $sec=substr($SQLDateTime,17,2);
> if ($year=="0000"){
> return "Unknown";
> }else{
> $DateTime=$day.'-'.$month.'-'.$year.' '.$hour.':'.$min.':'.$sec;
> }
> return $DateTime;
> }
>
> /* makes date SQL friendly, note that it takes dates with fewer digits for
> any portion of the date field, you may have to modify if you want different
> asumptions as to what century a date belongs to
> */
>
> function toSQLdate($AusDate){
> // split date up, first find delimiter used.
>
> if(strpos($AusDate,'-')){
> $delim='-';
> }else if(strpos($AusDate,'/')){
> $delim='/';
> }else{
> $date=false;
> return $date;
> break 2;
> }
> // find first break in date
> $breakPos1=strpos($AusDate,$delim);
> $breakPos2=strpos($AusDate,$delim,$breakPos1+1);
> $day=substr($AusDate,0,$breakPos1);
> if (strlen($day)==1){
> $day='0'.$day;
> }
> $month=substr($AusDate,$breakPos1+1,$breakPos2-$breakPos1-1) ;
> if (strlen($month)==1){
> $month='0'.$month;
> }
> $year=substr($AusDate,$breakPos2+1,strlen($AusDate)-$breakPo s2);
> if (strlen($year)==1){
> $year='200'.$year;
> }
> if (strlen($year)==2){
> if ($year>40){
> $year='19'.$year;
> }else{
> $year='20'.$year;
> }
> }else if(strlen($year)<>4){
> $date=false;
> return $date;
> break 2;
> }
> $date=$year.'-'.$month.'-'.$day;
> return $date;
> }
> ?>

Consider this instead:

//this function fort making sql dates readable to non-logical date users...

function toAusDate($sqlDate)
{
return toAusDateTime($sqlDate, true);
}

// same as above, but handles time as well

function toAusDateTime($sqlDateTime, $showTime = false)
{
if (intval(substr($sqlDateTime, 0, 4)) === 0)
{
return 'Unknown';
}
else
{
return date(
'd-m-Y' . ($showTime ? ' H:i:s' : ''),
strtotime($sqlDateTime));
}
}

/* makes date SQL friendly, note that it takes dates with fewer digits for
any portion of the date field, you may have to modify if you want different
asumptions as to what century a date belongs to
*/

function toSQLdate($ausDate)
{
// split date up, first find delimiter used.
$date = preg_split('/[-\/]/', $ausDate, -1, PREG_SPLIT_NO_EMPTY);

$day = $date[0];
$month = $date[1];
$year = intval($date[2]);

if ($year < 10)
{
$year += 2000;
}
else if ($year < 100)
{
if ($year > 40)
{
$year += 1900;
}
else
{
$year += 2000;
}
}
else if (strlen($year) > 4)
{
return false;
}

return date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
}
?>


X-Post & F'up2 comp.lang.php

PointedEars

Re: Date field in DD/MM/YYY

I prefer to store my dates in YYYYMMDD format on my tables for
compactness and readability, and worked up a bunch of functions for
working with them, which also includes validating mm/dd/yyyy input
(data entry people hate popup lists for date entry).


// days difference between dates input is in YYYYMMDD format.
function daysbetweendatenums($startdatenum, $enddatenum) {
$jstart = gregoriantojd(
substr($startdatenum,4,2)
,substr($startdatenum,6,2)
,substr($startdatenum,0,4));
$jend = gregoriantojd(
substr($enddatenum,4,2)
,substr($enddatenum,6,2)
,substr($enddatenum,0,4));
return intval($jend - $jstart);
}


// add or subtract days/months/years from date to create new date
// Input YYYYMMDD and the days,months,years to add or subtract
function datenumadjust($datenum, $days = 0, $months = 0, $years = 0) {
$newdate = date("Ymd",mktime(0,0,0
,substr($datenum,4,2)+$months
,substr($datenum,6,2)+$days
,substr($datenum,0,4)+$years));
return intval($newdate);
}


/* convert a readable date number "YYYYMMDD" to a DATE() compatible
value */
function datenumtounix($dval) {
$dval=mktime(0,0,0,substr($dval,4,2),substr($dval,
6,2),substr($dval,0,4));
return $dval;
}

/* convert a readable date number YYYYMMDD to a string date "MM/DD/
YYYY"
** input neumeric: YYYYMMDD
** output string: "mm/dd/yyyy"
*/
function datenumtostring($nval) {
if($nval>0){
$dval = substr($nval,4,2)."/".substr($nval,
6,2)."/".substr($nval,0,4);
} else {
$dval = "";
}
return $dval;
}

/* convert (validate) american date string value to a neumeric date
value
** inputs:
** date string in the format of: "mm/dd/yyyy" or "mm-dd-yyyy" or
"mm.dd.yyyy"
** with 2 or 4 digit years (2 digit numbers above 20 are assumed in
the 1900s)
** or even YYYYMMDD for us programmer types.
** lowest year acceptable - default 1920
** greatest year accepable - default 2300
** return 0 if empty
** result -1 if date is out of range or found to be invalid */

function datestringtonum($input, $lowyear = 1920, $maxyear = 2300 ) {
$sdate = "";
// convert possible YYYYMMDD entry to MM/DD/YYYY string
if($input > 10000000 and $input < 99999999){
$input = datenumtostring($input);
}
// split date based on different seperators
if ( strpos($input,"/") != 0) { // if seperators are '/'s...
$sdate = explode("/",$input);
} else if ( strpos($input,"-") != 0) { //if seperators are '-'s
$sdate = explode("-",$input);
} else if ( strpos($input,".") != 0) { //if sperators are '.'s
$sdate = explode(".",$input);
}
// if array, so far so good.
$ndate = 0;
if( is_array($sdate)){
// 2 digit years to 4 digit years
if ( $sdate[2] < 99 ) { //check/correct for 2 digit year
$sdate[2] = $sdate[2] + ( $sdate[2] < 20 ? 2000 : 1900 );
}
// check if date is a valid calendar date
if (checkdate($sdate[0],$sdate[1],$sdate[2])) {
//check if date falls within year limits
if ( $sdate[2] < $lowyear or $sdate[2] > $maxyear ) { //
make sure date is within acceptable range
$ndate = -1;
} else {
// build a number value YYYYMMDD
$ndate = $sdate[2].sprintf("%02d",
$sdate[0]).sprintf("%02d",$sdate[1]);
}
} else {
$ndate = -1;
}
//if not empty, then it's a bad date string.
} elseif (!empty($input)) {
$ndate = -1;
}
return $ndate;
}

Re: Date field in DD/MM/YYY

..oO(larry@portcommodore.com)

>I prefer to store my dates in YYYYMMDD format on my tables for
>compactness and readability

This is not a problem as long as a proper date type is used in the table
(DATE or DATETIME for example). In MySQL dates are stored as YYYY-MM-DD
strings, but YYYYMMDD is also possible in a numeric context.

>and worked up a bunch of functions for
>working with them

Looks pretty complicated. MySQL and PHP have a lot of date functions,
which already can do most of that. With properly stored dates such
calculations are as easy as adding two integers. MySQL is also able to
return Unix timestamps, which can then easily be used with PHP's date
functions.

>function datestringtonum($input, $lowyear = 1920, $maxyear = 2300 ) {
> $sdate = "";
> // convert possible YYYYMMDD entry to MM/DD/YYYY string
> if($input > 10000000 and $input < 99999999){
> $input = datenumtostring($input);
> }
> // split date based on different seperators
> if ( strpos($input,"/") != 0) { // if seperators are '/'s...
> $sdate = explode("/",$input);
> } else if ( strpos($input,"-") != 0) { //if seperators are '-'s
> $sdate = explode("-",$input);
> [...]

You might want to consider to use regular expressions, which makes it
very easy to test against different patterns.

Micha

Re: Date field in DD/MM/YYY

On Oct 27, 11:01 am, Michael Fesser wrote:
> .oO(la...@portcommodore.com)
>
> >I prefer to store my dates in YYYYMMDD format on my tables for
> >compactness and readability
>
> This is not a problem as long as a proper date type is used in the table
> (DATE or DATETIME for example). In MySQL dates are stored as YYYY-MM-DD
> strings, but YYYYMMDD is also possible in a numeric context.
>
> >and worked up a bunch of functions for
> >working with them
>
> Looks pretty complicated. MySQL and PHP have a lot of date functions,
> which already can do most of that. With properly stored dates such
> calculations are as easy as adding two integers. MySQL is also able to
> return Unix timestamps, which can then easily be used with PHP's date
> functions.
>
[snip]
>
> You might want to consider to use regular expressions, which makes it
> very easy to test against different patterns.
>
> Micha

For the format, I was thinking of long term or DB agnostic, where I
may not have MySQL compatible fields, so I went with integer.

As for regular expressions haven't quite got the hang of those yet (at
least it is all in one place so I can fix it when I do.)

Re: Date field in DD/MM/YYY

larry@portcommodore.com wrote:
> On Oct 27, 11:01 am, Michael Fesser wrote:
>> .oO(la...@portcommodore.com)
>>
>>> I prefer to store my dates in YYYYMMDD format on my tables for
>>> compactness and readability
>> This is not a problem as long as a proper date type is used in the table
>> (DATE or DATETIME for example). In MySQL dates are stored as YYYY-MM-DD
>> strings, but YYYYMMDD is also possible in a numeric context.
>>
>>> and worked up a bunch of functions for
>>> working with them
>> Looks pretty complicated. MySQL and PHP have a lot of date functions,
>> which already can do most of that. With properly stored dates such
>> calculations are as easy as adding two integers. MySQL is also able to
>> return Unix timestamps, which can then easily be used with PHP's date
>> functions.
>>
> [snip]
>> You might want to consider to use regular expressions, which makes it
>> very easy to test against different patterns.
>>
>> Micha
>
> For the format, I was thinking of long term or DB agnostic, where I
> may not have MySQL compatible fields, so I went with integer.
>
> As for regular expressions haven't quite got the hang of those yet (at
> least it is all in one place so I can fix it when I do.)
>
>

You should check out the SQL standard. All real RDB's have a DATE type
of some sort, and all I know of are compatible with that standard.

You're just making your job a lot harder.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================