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 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;
}
?>
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
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
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.
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
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
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.
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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 =...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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 "...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |