mysql 3.2 date statement

[QuintonV]

HI

Im using the curent latest stable version of mysql on a windows platform im using timestamp on a field to keep the latest time of when a record was modified now I want to ad the default date when a record was created, when I used to have my db in access all I did was go into the design of the field and put date() in the default value that would then capture the curent date of the server into the field whenever some1 added a record, no I want to do that in mysql and have tried to put date() and now() into the default value but it keeps on puting 0000-00-00 00:00:00 into the field whenever some1 adds a new record is there any other way I can get the curent date and time from the server of the db in the field so I can keep track of when a record was added or created?

Thanx

 

[QuintonV]

Thanx to everyone that tried to help out but iv'e solved it myself all I had to do is add 2 timestamps to my table in mysql it puts the timestamp in both fields when a new record is created but only updates the first timestamp in the table when a record gets changed or updated thus the second timestamp field will remain the same for record creation date and can be manualy changed by runing a NULL or now() update on it

Quinton

 

[QuintonV]

It seemed that helped me out it works fine when I ad a record from the mysql comand prompt, but when I ad a record from my .asp page it doesnt log the date it only pts it in the firs ttimestamp field and not the other. Can anyone please tell me how I can get it to take the creation date too.

One confused mysql user

 

[Broccoli2]

Can you post your

INSERT

and

UPDATE

queries here along with your table definition - that would help.

-Rob

 

[QuintonV]

here we go thank you rob

thats the complete insert record function and at botomb my tables defintions

************************************************************
<form name=&quot;form2&quot; method=&quot;POST&quot; action=&quot;<%=MM_editAction%>&quot;>
<table border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; bordercolor=&quot;#CC0000&quot; width=&quot;632&quot;>
<tr>
<td>
<table border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; bordercolor=&quot;#FFFFFF&quot; width=&quot;629&quot;>
<tr>
<td bgcolor=&quot;lightblue&quot;> <font size=&quot;2&quot;><b>Members name </b></font></td>
<td bgcolor=&quot;lightgrey&quot;>
<input type=&quot;text&quot; name=&quot;memname&quot;>
</td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Contact person initials/name/surname</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<input type=&quot;text&quot; name=&quot;initials&quot; size=&quot;6&quot; maxlength=&quot;6&quot;>
<input type=&quot;text&quot; name=&quot;name&quot;>
<input type=&quot;text&quot; name=&quot;surname&quot;>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Company name</font></b></td>
<td bgcolor=&quot;lightgrey&quot;>
<input type=&quot;text&quot; name=&quot;compname&quot;>
</td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Telephone no.</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<input type=&quot;text&quot; name=&quot;telno&quot; value=&quot;tel&quot;>
<input type=&quot;text&quot; name=&quot;celno&quot; value=&quot;cel&quot;>
<input type=&quot;text&quot; name=&quot;faxno&quot; value=&quot;fax&quot;>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Member &amp; funeral no.</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<input type=&quot;text&quot; name=&quot;memno&quot;>
<input type=&quot;text&quot; name=&quot;bonfunerno&quot; value=&quot;<%= Request.Form(&quot;fno&quot %>&quot;>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Call status</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<select name=&quot;calstat&quot;>
<option value=&quot;resolved&quot;>Resolved</option>
<option value=&quot;n/resolved&quot; selected>N/Resolved</option>
</select>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Query</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<select name=&quot;query&quot;>
<%
while (!queries.EOF) {
%>
<option value=&quot;<%=(queries.Fields.Item(&quot;query&quot.Value)%>&quot;><%=(queries.Fields.Item(&quot;query&quot.Value)%></option>
<%
queries.MoveNext();
}
if (queries.CursorType > 0) {
if (!queries.BOF) queries.MoveFirst();
} else {
queries.Requery();
}
%>
</select>
<img src=&quot;images/spacer_clear.gif&quot; width=&quot;33&quot; height=&quot;7&quot;>
<input type=&quot;text&quot; name=&quot;other&quot;>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>To Do</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<textarea name=&quot;todo&quot; cols=&quot;50&quot; wrap=&quot;VIRTUAL&quot;></textarea>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Solution</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<textarea name=&quot;solution&quot; cols=&quot;50&quot; wrap=&quot;VIRTUAL&quot;></textarea>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Notes</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<textarea name=&quot;notes&quot; cols=&quot;50&quot; wrap=&quot;VIRTUAL&quot;></textarea>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>This call is being loged
by</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<input type=&quot;text&quot; name=&quot;coby&quot; value=&quot;<%= Session(&quot;mm_username&quot %>&quot;>
</b></font></td>
</tr>
<tr>
<td bgcolor=&quot;lightblue&quot;><b><font size=&quot;2&quot;>Forward to</font></b></td>
<td bgcolor=&quot;lightgrey&quot;> <font size=&quot;2&quot;><b>
<select name=&quot;ptop&quot;>
<%
while (!users.EOF)
{
%>
<option value=&quot;<%=(users.Fields.Item(&quot;USERNAME&quot.Value)%>&quot; ><%=(users.Fields.Item(&quot;USERNAME&quot.Value)%></option>
<%
users.MoveNext();
}
if (users.CursorType > 0) {
if (!users.BOF) users.MoveFirst();
} else {
users.Requery();
}
%>
<option value=&quot;<%= Session(&quot;mm_username&quot %>&quot; selected><%= Session(&quot;mm_username&quot %></option>
</select>
</b></font></td>
</tr>
</table>
</td>
</tr>
</table>
<p>
<input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot; onClick=&quot;tmt_confirm('Are%20you%20sure%20you%20want%20to%20log%20this%20record?');return document.MM_returnValue&quot;>
<input type=&quot;reset&quot; name=&quot;Submit2&quot; value=&quot;Reset&quot;>
</p>
<input type=&quot;hidden&quot; name=&quot;MM_insert&quot; value=&quot;true&quot;>
</form>
***********************************************************
| ID | int(11) | | PRI | NULL | auto_increment
| compname | varchar(50) | YES | | NULL |
| bonfunerno | varchar(50) | | | |
| memno | varchar(50) | | MUL | |
| name | varchar(50) | | | |
| surname | varchar(50) | | | |
| initials | varchar(50) | | | |
| memname | varchar(50) | | | |
| telno | varchar(50) | YES | | NULL |
| celno | varchar(50) | YES | | NULL |
| faxno | varchar(50) | YES | | NULL |
| calstat | varchar(50) | YES | | NULL |
| query | varchar(50) | YES | | NULL |
| oth | varchar(50) | YES | | NULL |
| todo | longtext | YES | | NULL |
| solution | longtext | YES | | NULL |
| notes | longtext | YES | | NULL |
| coby | varchar(50) | YES | | NULL |
| ptop | varchar(50) | YES | | NULL |
| actdone | timestamp(14) | YES | | NULL |
| actdate | timestamp(14) | YES | | NULL |
+------------+---------------+------+-----+---------+----------------
21 rows in set (0.17 sec)

thank you

 

[Broccoli2]

Sorry, can't see any SQL statements here - I guess you'll need someone who knows ASP.

-Rob

 

[QuintonV]

thanx 4 your help rob , do u knowe of any simpler way i can keep track of the dates? just creation date and latest modified date