Best Method for Storing Date Vaues in Database?

[eluser]JamesTaylor[/eluser]
What method do people find best to store dates in a DB??

As i continue to learn PHP and now CodeIgniter i have developed a number of sites that use dates for certain aspects such as the date of an event or the date a news article was published etc.

I always seem to end up wondering what is the best way to store these dates in a database given that i tend to ask a user to fill out the date, via a form, in 3 separate text input fields - one for day, one for month, one for year.

So far i have done it a couple of ways, i have stored the the date parts as separate DB fields (day, month, year) i have converted the users input to a unix timestamp and stored that (as an integer at the time i think!)... i think at some point early on i have even stored both versions! I see there are then further formats for storing dates like DATE / DATETIME / TIMESTAMP / TIME / YEAR.

I feel as if i should be working with dates in a more structured fashion and have done some reading but i am still not sure which way would be preferred, i guess i'm looking for ease of use given how i tend to ask for dates to be inputted via a form and also for functionality once stored for aspects such as searching against - records earlier / later than a specific date or between specific dates etc.

Looking forward to peoples thoughts!

James

[eluser]rogierb[/eluser]
Go for the DATE format (In MySQL). Easy to using with 'BETWEEN' and other sql. Time is a whole different story. I've used both TIME and DECIMAL. The latter when I needed to do a lot of calculating.
10.5 + 8.25 is a lot easier then 10:30+8:15:-)

[eluser]umefarooq[/eluser]
best way to save date is DATETIME later you can use mysql function date_format() to get formatted date from database like day, month, year. same like php date() function you can format you date

check the link
http://dev.mysql.com/doc/refman/5.1/en/d...ate-format
http://www.w3schools.com/SQL/func_date_format.asp

[eluser]saidai jagan[/eluser]
I go for DATETIME

[eluser]JamesTaylor[/eluser]
Thanks for the reply's so far!

is there great difference between DATE and DATETIME? or is just that DATETIME always stores a time value as well as the date? In my current instance i am only storing the date, no time value.

So i'm just having a little play around:

i am using DATE in the DB and have queried the DB to retrieve a row, i then outputting the date in my view by using

Code:

<?php echo ($NewsItems->NewsDate); ?>

which is giving me a date of YYYY-MM-DD, how would i just output YYYY for example?

When i try:

Code:

<?php echo date("Y", $NewsItems->NewsDate); ?>

it just trows up errors.

[eluser]rogierb[/eluser]
The easiest way:

Code:

<?php echo date("Y", strtotime($NewsItems->NewsDate)); ?>

[eluser]JamesTaylor[/eluser]
Thanks, i wasn't too far off was just missing the extra strtotime syntax... the joys of learning makes doing the basics take way too much time!!

[eluser]jedd[/eluser]
[quote author="JamesTaylor" date="1258663919"]
is there great difference between DATE and DATETIME?
[/quote]

If only there was a [url="http://dev.mysql.com/doc/refman/5.1/en/datetime.html"]place on the Internet somewhere that described MySQL's various date and time formats[/url], eh?

which is giving me a date of YYYY-MM-DD, how would i just output YYYY for example?

rogierb's date() function, or just substr( $date_string, 0, 4 )