Date formatting help

[fife]

Hi Guys

 

I am now heading to a section in my website were is will be using dates.  I'll be comparing, adding and subtracting dates from each other.  I am just wondering if anybody know of any decent tutorials out there to best describe how to store dates in a database which will best suit this purpose.  At the moment I am storing them in the English format of dd/mm/yyyy.  I have read a few articles but lots of people have different ideas on how this is done.  Im looking for the more professional approach.

 

Thanks

[voip03]

The default way to store a date in MySQL is with the type DATE. Below is the proper format of a DATE.

 

•YYYY-MM-DD

•Date Range: 1000-01-01 to 9999-12-31

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

[voip03]

htmlGo to MySQL Reference - 10.5. Data Type Storage Requirements

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.

Search for: Storage Requirements for Date and Time Types

 

Dates are internally stored as A three-byte integer packed as DD + MM×32 + YYYY×16×32

 

But, if you select a date column for display, it has to be shown in some way, so it comes out as 0000-00-00. It is not stored as a char(10) with that specific format.

 

If, for display, you need to see a specific format, you can convert it to VARCHAR in a specific format using Date_Format(). However, bear in mind that if you are using the query in a programming tool, this is not what you want to do. You want the raw date value, for display purposes, there will be a similar formatting function from whatever programming environment you use.

 

As you can see from the reference in DATE_FORMAT, you will want to use '%d-%m-%Y', e.g.

 

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1... FROM sometable ....