Inserting present date and time to a field of mysql table
Adding ( or inserting ) date to a mysql date field is one of the common requirement. Some time we have to add current date and time while inserting the record. With this every time a record is added, the present date and time also added along with the record. Before adding date or date and time we have to format the data in a format acceptable to the mysql date field. MySQL date field will accept only valid dates and times so proper formatting is a must and we will discuss here how a record with a date field and a date and time field is added to a mysql table. We have one date field and one date time field in our table so we will try to format for both the fields. Here are the formats.
For a date field it should be
YYYY-mm-dd
Example: 2005-12-26
For a date and time field it should be
YYYY-mm-dd H:i:s
Example: 2005-12-26 23:50:30
So with this here is one simple SQL query to add a record with a date and date & time field.
INSERT INTO dt_tb (dt,dt2) VALUES ('2004-05-05 23:56:25', '2005-06-12');
Adding present ( current ) date and time while inserting a record
Now let us try to add default date and time to the table. That is when ever a record is to be added, it will insert present date and time to the table. So we will format the present date and then present date with time. Let us take two variables $dt1 ( storing the present date value ) and $dt2 ( storing the present date and time ).
$dt1=date("Y-m-d");
$dt2=date("Y-m-d H:i:s");
Now our sql query with these two date values will be like this
INSERT INTO dt_tb(dt,dt2) VALUES ('$dt2', '$dt1');
However this way we are adding date and time to a field through a sql query. First we are calculating the current date and time value by using PHP and then storing that value in a field.
Is there a way to provide condition check while using INSERT in sql querry
John
03-02-2010
Sorry, but I can't get it to work. Why don't you include this as part of a practical database example. I've been all over the net just to stick a date field in my database. Nothing works - it can't be that hard to do a practial working example of this.
Matt McCarty
25-04-2010
@John... I have a variable list (one is a date to insert, i.e. $dtCreated=date('Y-m-d'); Then I have my INSERT statement with that variable (using PHP) $query = "INSERT INTO myTable VALUES ('$dtCreated')"; If you have more than one variable, order matters!
chan
20-08-2010
I need aprocedure which generates a table with columns week,start_dt,End_dt. Week should have all 52 weeks,start_dt should every weeks start day ,end_dt should have everyweeks end date.
Please help
Thanks
madhavi
24-04-2014
I need to alter table with current date and time in mysql
smo
14-11-2014
One practical example using PHP is added to this tutorial.
sadia
24-08-2015
Very helpful
deepa
30-05-2016
insted of php code i want html or javascript code..can u give us>
Shital Gamaji Patil
05-01-2017
how to add from and to date in a single column. or in single column how to add two dates.
smo1234
19-02-2017
You can store dates as string in a single column. You can use any delimiter to separate the dates, while retrieving you can use the same delimiter to separate them. But what is the problem in storing them in two different columns ?