Storing custom date into SQL table

[soltek]

Hey there,

 

this is a bit embarrassing, but I dont know how to store correctly, dates on a SQL table.

I've been googling it for a while and got confused.

 

So, I have a table, where the user would type a date, as 20/10/2012 23:30 inside a form field.

That form would store the date in the sql table.

Do you know any website that explains how to send the information properly?

 

Also, later I'll need to print on a page how much time lefts to that date - in this case, one year. Would a simple subtraction do?

 

 

Thanks!

[AyKay47]

1. http://www.tizag.com/mysqlTutorial/mysql-date.php

 

2. yes you would want to compare the date in your db and the current date.. you can use something like date_diff to do this.

[The Little Guy]

First, you want a column that is made for timestamps, so you would use a data type of "datetime".

Second, you don't want to store the value like like this "20/10/2012 23:30" in the database, you would store it like this "2012-10-20 23:30:00" (YYYY-MM-DD HH:MM:SS)

 

Now MySQL can easily do date/datetime functions on your dates. What you have is a valid date format, but NOT a valid MySQL date format.

[fenway]

STR_TO_DATE() is your friend.