Page 1 of 1

Dates - Interesting but need help.

Posted: Mon Feb 28, 2005 6:56 am
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..

Posted: Mon Feb 28, 2005 8:33 am
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!

Posted: Mon Feb 28, 2005 8:44 am
by mohson
so what your saying is I should store the date colomns as int(10)???

that way I could what ever I want???

Posted: Mon Feb 28, 2005 8:46 am
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 ;)

Posted: Mon Feb 28, 2005 8:51 am
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.

Posted: Mon Feb 28, 2005 9:10 am
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?

Posted: Mon Feb 28, 2005 9:33 am
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.