By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Date field in DD/MM/YYY

P: n/a
Jes
Dear all

I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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

Thanks
Jesmond

Oct 27 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Jes wrote:
Dear all

I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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

Thanks
Jesmond
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, but
it does the job, and I havn't got around to tidying it up. (If it aint
broke.......)

regards,
Chris Roy-Smith

<?php
//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 )-$breakPos2);
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;
}
?>

Oct 27 '07 #2

P: n/a
Jes wrote:
I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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 ?
alert('27/10/2007'.split('/').reverse().join(''))

--
Bart

Oct 27 '07 #3

P: n/a
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.......)

<?php
//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 )-$breakPos2);
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:

<?php
//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
Oct 27 '07 #4

P: n/a
Bart Van der Donck wrote:
Jes wrote:

>>I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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 ?


alert('27/10/2007'.split('/').reverse().join(''))
Close: alert('27/10/2007'.split('/').reverse().join('-'))
Mick.
Oct 27 '07 #5

P: n/a
Michael White wrote:
Bart Van der Donck wrote:
>Jes wrote:
>>I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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 ?

alert('27/10/2007'.split('/').reverse().join(''))
^^
>>
Close: alert('27/10/2007'.split('/').reverse().join('-'))
Both will work with MySQL:

http://dev.mysql.com/doc/refman/4.1/en/datetime.html

But the database operation will still fail if client-side script support is
not present or enabled, so this really should be done server-side.
PointedEars
--
realism: HTML 4.01 Strict
evangelism: XHTML 1.0 Strict
madness: XHTML 1.1 as application/xhtml+xml
-- Bjoern Hoehrmann
Oct 27 '07 #6

P: n/a
Michael White wrote:
Bart Van der Donck wrote:
>Jes wrote:
>>>I have a date field on a HTML form where the user is asked to key in
dd/mm/yyyy
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 ?
> alert('27/10/2007'.split('/').reverse().join(''))

Close: alert('27/10/2007'.split('/').reverse().join('-'))
I had put it in YYYYMMDD because that was the request of the original
poster (which was without the Mini). Obviously, MySQL will accept much
more than YYYYMMDD or YYYY-MM-DD. Please refer to my articles some
time ago:

http://groups.google.com/group/comp....6188acd0582e5c
http://groups.google.com/group/comp....a28e20b33aec22

Of course, the initial format of '27/10/2007' should be checked before
executing split.reverse.join on it.

I join the statement of Thomas Lahn that all this should better be
done server-side.

--
Bart

Oct 29 '07 #7

P: n/a
In comp.lang.javascript message <11**********************@k79g2000hse.go
oglegroups.com>, Mon, 29 Oct 2007 05:24:07, Bart Van der Donck
<ba**@nijlen.composted:
>
Of course, the initial format of '27/10/2007' should be checked before
executing split.reverse.join on it.

I join the statement of Thomas Lahn that all this should better be
done server-side.
IMHO, the date should be validated (as Gregorian) client-side, so that
simple errors don't waste a transaction. Little code is needed.

Depending on the application, it may make sense to do more client-side
validation - for example, few hotels want to take bookings for past
dates or ones far ahead.

If client-side validation can ensure, or nearly ensure, that normal
users' mistakes are caught client-side, then server-side checking, or
most of it, only needs to defend against nasty attack. If the client-
side Javascript validates a date string as ISO-8601 compliant, and the
server receives instead an FFF date string, then there is no call for
the server-side code to be user-sympathetic.

--
(c) John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v6.05 IE 6.
Web <URL:http://www.merlyn.demon.co.uk/- w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/- see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.
Oct 29 '07 #8

P: n/a
Dr J R Stockton wrote:
IMHO, the date should be validated (as Gregorian) client-side, so that
simple errors don't waste a transaction. Little code is needed.
That may be a wise strategy.
Depending on the application, it may make sense to do more client-side
validation - for example, few hotels want to take bookings for past
dates or ones far ahead.
For this kind of date stuff I've had excellent experiences with
http://www.mattkruse.com/javascript/calendarpopup/
If client-side validation can ensure, or nearly ensure, that normal
users' mistakes are caught client-side, then server-side checking, or
most of it, only needs to defend against nasty attack. If the client-
side Javascript validates a date string as ISO-8601 compliant, and the
server receives instead an FFF date string, then there is no call for
the server-side code to be user-sympathetic.
Generally spoken, I've always been a bit reluctant in regard to big
client scripting projects. Coding at the server is often simpler and
more robust IMHO.

--
Bart

Oct 30 '07 #9

P: n/a
Jes
On Oct 27, 1:42 am, Jes <jesmondspit...@gmail.comwrote:
Dear all

I have adatefield on a HTML form where the user is asked to key in
dd/mm/yyyy
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

Thanks
Jesmond
Thanks Roy

Oct 31 '07 #10

P: n/a
Jes
On Oct 27, 2:26 pm, Bart Van der Donck <b...@nijlen.comwrote:
Jes wrote:
I have adatefield on a HTML form where the user is asked to key in
dd/mm/yyyy
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 ?

alert('27/10/2007'.split('/').reverse().join(''))

--
Bart
Thanks

Oct 31 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.