473,320 Members | 1,881 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date field in DD/MM/YYY

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
10 2514
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: jason | last post by:
I'm tearing out my hair here: ACCESS 2000: When I attempt to overwrite a date in my date field with a new user selected valid date eg: 05/23/99 my date field changes to the TIME I updated the...
7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
3
by: BlackFireNova | last post by:
This concerns an Access 2002 (XP) database. There are two fields, "Notes" (Memo Field) and "Notes Update" on a form (among others) which I am concerned with here. Problem: I need to be able...
10
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for...
3
by: captain | last post by:
Below is the sql for data with same date need to extract + or - 5 days data of same date also. How to also get data of + and - days related to same date. SELECT IM.Area, IM.Location,...
1
by: EV | last post by:
We have a personnel database that we want to set up...For time off we will have a start date field and an end date field as well as a field for the number of hours. We need to determine how many...
1
by: Monica Roman | last post by:
Hello, help please. I have a little macro that copies the date from a date/time field to a memo field, BUT instead of going to the memo field it finds the first date/time field and tries to copy...
1
by: Graham Feeley | last post by:
I have a date field named rcdate it is a general date eg: 12/08/2006 3:30:00 PM 12/08/2006 3:00:00 PM I used to update another empty field named rdate with the with using in a update query "...
6
by: Luvin lunch | last post by:
Hi, I'm new to access and am very wary of dates as I have limited experience in their manipulation and I know if they're not done properly things can turn ugly quickly. I would like to use a...
7
by: sheri | last post by:
I have a field called date, the date is in the field like this 70925 (which means Sept. 25, 2007). I have another field called day, it is a text field. How do I write a query to populate the day...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.