Dates - Interesting but need help.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Dates - Interesting but need help.

Post by mohson »

Am I right in thinking you can save yourself soo much trouble if you set up a date field as integer instead of date??

Ive been told that you can reformat the way your date is printed (as I have done below)

Code: Select all

DATE_FORMAT(dateoflastcontact, '%d/%m/%y') 
AS dateoflastcontact, DATE_FORMAT(datecontactagain,  '%d/%m/%y') 
AS datecontactagain
but that you MUST insert it in standard US format which is yy-mm-dd

now my problem is that when I have a search box which gives the user an option to searcha last contact date of an organisation - I cant search in the form of my my UK date setting as I have done above. The only way the search query works is if I enter the US format.

example last contact date is 28th of febuary 2005 - I must enter this in STANDARD MYSQL FORMAT which is 05-02-28 - due to my reformatting above I make this appear as 28-02-05 - which is fine..

BUT when I search for 28-02-05 I get no results and when I search for 05-02-28 I still dont get any results BUT when I search for 2005-02-28 then I GET!! my results.

Im thinking I should just set the date fields as integer and store the dates as month-year i.e Feb - 05 which will make the search much easier..

Any advice or tips would be much appreciated..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

US date format is dd/mm/yyyy and dd/mm/yy btw..

YYYY-MM-DD is the format used because of a few reasons:
  1. it sorts perfectly on it's own, no hand holding or monkeying around
  2. it's probably the only agreeable international date format
I would not store the date in any "custom" format other than a straight int(10) unsigned. So month-year is inadvisable.



8000 posts woo!
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

so what your saying is I should store the date colomns as int(10)???

that way I could what ever I want???
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Congrats Feyd! on having reached 8000 milestone. you were looking forward to this weren't you :lol:
8000 posts :!: 8O ooooh! *swoons*

*wakes up* So next what, 10000 mark huh ;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

as an int(10) unsigned, you store it as an unix timestamp. Do not play around with making it fit into any format like month-year.. do that on output only.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

thanks feyd for your reply - I see what you mean by playing around with it (only do it when you print) BUT theres a problem - what about when I search It means I need to use the YYYY-MM-DD format when the user inputs a date to search. is there noway that the user would be able to put in 'Feb' in the search text box or even '2' for the month and it come back with a valid search?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sure they can... but you must convert it to the format. If you make it so they can only input correct format, it may be easier. I like using a calendar selector personally.
Post Reply