Forums
This topic is locked
MYSQL DATE and TIME
01 Sep 2006 10:22:00 Doc BEATs posted:
i am upgrading a website that i originally built on MS ACCESS. The Problem is in ACCESS i am able to create a column with data type DATE set and default value set to =DATE(). aswell i have a column TIME with default value set to =TIME(). now in mysql i have created this same table with these rows DATE default CURRENT_DATE, TIME default CURRENT_TIME. none work i've tried CURRENT_DATE(), CURDATE(), CURDATE, CURTIME, CURRENT_TIME() and nothing none work. i am scripting in ASP is there a fix for this or work around my server is running MYSQL 4.0.27THANKS
Replies
Replied 01 Sep 2006 11:16:12
01 Sep 2006 11:16:12 Roddy Dairion replied:
in mysql 4.0 try DATE(NOW()) for date and TIME(NOW())
Replied 01 Sep 2006 18:55:05
01 Sep 2006 18:55:05 Doc BEATs replied:
well i tried that too and nothing i still get 0's. this works for you? i set the data type for the column to date, and set the Default to DATE(NOW()) , nothing i then open mysql again and edited the same column but left default alone and changed Length/value to DATE(NOW()) and still nothing...
if i use timestamp and set the default to CURRENT_TIMESTAMP that works i get the server day and time. but i just want to split this up two two column instead of one.
THANKS
if i use timestamp and set the default to CURRENT_TIMESTAMP that works i get the server day and time. but i just want to split this up two two column instead of one.
THANKS
Replied 04 Sep 2006 15:43:48
04 Sep 2006 15:43:48 Roddy Dairion replied:
CURDATE() is used for mysql 4.0 sorry for that mistake.
Replied 19 Nov 2007 14:21:55
19 Nov 2007 14:21:55 Michael Kang'ari replied:
Hi,
I am using PHPMyadmin 2.8.2.4 offline and version 2.11.0 online. My problem is more or less similar to this:
I want to register the date when a member signs up. In the default value, I have tried now(), CURDATE(), date(now()) but all am getting is 0000:00:00 as the default value. I think sometime back it used to work but now it's not.
I don't want to risk fetching the date with a form from the users computer -> php echo date('Y,m,d'); just in case <img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle><img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle>.
Please assist.
Best Rgds.,
Mike
I am using PHPMyadmin 2.8.2.4 offline and version 2.11.0 online. My problem is more or less similar to this:
I want to register the date when a member signs up. In the default value, I have tried now(), CURDATE(), date(now()) but all am getting is 0000:00:00 as the default value. I think sometime back it used to work but now it's not.
I don't want to risk fetching the date with a form from the users computer -> php echo date('Y,m,d'); just in case <img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle><img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle>.
Please assist.
Best Rgds.,
Mike
Replied 19 Nov 2007 14:40:53
19 Nov 2007 14:40:53 Roddy Dairion replied:
Make sure the format of that field is set as date only. Once this is done it should record it as yyyy-mm-dd i.e. 2007-11-19
then you have date functions like curdate() which will retrieve the actual yyyy-mm-dd.
Then in your insert query should look like this
<pre id=code><font face=courier size=2 id=code>
insert into tablename(fielddate) values(curdate()) </font id=code></pre id=code>
This should insert the date in the field used to store date as 2007-11-19
then you have date functions like curdate() which will retrieve the actual yyyy-mm-dd.
Then in your insert query should look like this
<pre id=code><font face=courier size=2 id=code>
insert into tablename(fielddate) values(curdate()) </font id=code></pre id=code>
This should insert the date in the field used to store date as 2007-11-19
Replied 19 Nov 2007 14:58:14
19 Nov 2007 14:58:14 Michael Kang'ari replied:
Hi,
My deepest appreciation for the prompt response.
I have followed these very instructions before but it is not working.
My field type is date, coalition none,
Where youhave default value, I put CURDATE().
When the PHPMyadmin refreshes, The default value is still 0000:00:00
An adjacent field that has timestamp as the field type and On Update Current Timestamp as default is working OK.
Do you think it could be a bug with PHP Myadmin?
Best Regards,
Mike
My deepest appreciation for the prompt response.
I have followed these very instructions before but it is not working.
My field type is date, coalition none,
Where youhave default value, I put CURDATE().
When the PHPMyadmin refreshes, The default value is still 0000:00:00
An adjacent field that has timestamp as the field type and On Update Current Timestamp as default is working OK.
Do you think it could be a bug with PHP Myadmin?
Best Regards,
Mike
Replied 19 Nov 2007 15:53:30
19 Nov 2007 15:53:30 Roddy Dairion replied:
first of all the field shudn't hve a default value. The default format also should be 0000-00-00 not 0000:00:00. Timestamp picks up the date an time as 2007-11-19 13:36:35 you can always use that, but be careful timestamp picks the datetime automatically when you insert it but also when the record is altered also. Meaning if a record was inserted yesterday and you change something today the timestamp will be set as today not yesterday.
Ok for your problem now (i don't use phpmyadmin) as its a pain. But like i said curdate() should work fine with the query i've sent you.
When i've used phpmyadmin 2.8 to create this field all i did was put a fieldname where it says field and set it as DATE and thats it.
Then i used this query <pre id=code><font face=courier size=2 id=code>insert into testtimestmp(test,name) values(curdate(),'test33') </font id=code></pre id=code> where test is the field that retains the date.
Ok for your problem now (i don't use phpmyadmin) as its a pain. But like i said curdate() should work fine with the query i've sent you.
When i've used phpmyadmin 2.8 to create this field all i did was put a fieldname where it says field and set it as DATE and thats it.
Then i used this query <pre id=code><font face=courier size=2 id=code>insert into testtimestmp(test,name) values(curdate(),'test33') </font id=code></pre id=code> where test is the field that retains the date.
Replied 19 Nov 2007 16:17:07
19 Nov 2007 16:17:07 Michael Kang'ari replied:
Hi,
Thanks again.
My main problem is not timestamp. The problem is at date field.
Sorry sir, I got a little confused by your response:
Are you saying in the field that I want to record the date that a member has signed in, I leave the default value blank, then I use your SQL statement.
does this mean that if I had an insert form and I leave out the field value, the field will automatically be updated?
Thanks Again <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
Thanks again.
My main problem is not timestamp. The problem is at date field.
Sorry sir, I got a little confused by your response:
Are you saying in the field that I want to record the date that a member has signed in, I leave the default value blank, then I use your SQL statement.
does this mean that if I had an insert form and I leave out the field value, the field will automatically be updated?
Thanks Again <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
Replied 19 Nov 2007 16:17:17
19 Nov 2007 16:17:17 Michael Kang'ari replied:
Hi,
Thanks again.
My main problem is not timestamp. The problem is at date field.
Sorry sir, I got a little confused by your response:
Are you saying in the field that I want to record the date that a member has signed in, I leave the default value blank, then I use your SQL statement.
does this mean that if I had an insert form and I leave out the field value, the field will automatically be updated?
Thanks Again <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
Thanks again.
My main problem is not timestamp. The problem is at date field.
Sorry sir, I got a little confused by your response:
Are you saying in the field that I want to record the date that a member has signed in, I leave the default value blank, then I use your SQL statement.
does this mean that if I had an insert form and I leave out the field value, the field will automatically be updated?
Thanks Again <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>