date format ??

Anon

I want to create a table with date like :
create table reward (
thedate date NOT NULL,
....
);

What I need for dates is this format :
May 2001
June 1999

How can I do it please ?
Thanks

matto

"May 2001" is not a sql date format.
if you insist on storing it like this, use a character field.

or, the better way, store it as a normal db date (or a timestamp) and convert it to whatever you wish using php's date/time functions when retrieving it.

[deleted]

"if you insist on storing it like this, use a character field"

There is no consievable reason on earth why you would ever need to store the date as a string. It just doesn't exist.

Always store the date as a date/datetime/timestamp field, or as a timestamp stored in an integer field.

The final format that you print on the screen is defined in the query.

phigga

"There is no consievable reason on earth why you would ever need to store the date as a string. It just doesn't exist."

That's a pretty bold statement...

For instance, what if a client wanted to have a form field where they could enter the date of a magazine article. They want to put in "March 2000" instead of 2000-03-01.

Not the best case scenario, but hey....some clients are like that.

ace21

Bold but true statement...

If that is the case then convert that March 2000 into the correct datetime stamp in php.

Then when you retrieve it try using MONTHNAME(datetime stamp) to get the actual name of the month in your sql statement. read up on dealing with dates in sql statements to learn all the date functions. There are many that are very useful.

Ace21

[deleted]

I hoped someone would complain :-)

What the client want's to type has nothing to do with what you put into the database.

If the client want's to type 'March 2000' you make a function that accepts 'march 2000' and makes a timestamp out of it. Strtotime() is pretty good at that.

Once you have the timestamp in the database you can use PHP's date() function to get back to 'March 2000'

Point is: if you start literally putting 'March 2000' into the database, you are basically f*cking yourself up the bottom with a large aubergine. That string is useless. You can't calculate with it, so you can't use it in any queries except those where you happen to be looking for 'March' or '2000', and then you're just making slow queries.

What's more, the string uses a lot more space in the database than the timestamp.

phigga

Don't get me wrong here....all of the points that you just made are valid. And I agree, the string "March 2000" is useless.

But don't assume that the client automatically has some use for this string. If the only reason they want "March 2000" in a database is so that they can print it up real purty-like on a screen (that is, they do not wish to search on it, group by it, or otherwise), I'll be damned if I'm going to have them enter 2000-03-01, get an error until they can give the right format, store it in the database, and finally run it through date() whenever i want to print "March 2000" to the screen.

ace21

First of all keep assuming that they will never want to search on it or do whatever. Because I have never had a person change there mind. I just feel sorry for the sap who has to go back and change it when they do change there mind and they always do!

By the way they don't enter it 2001-03-01. They choose from dropdowns or whatever March 2001. You put it in to timestamp format (not hard) and then put it in the database. And the beauty of sql is that when you grab it out of the database you can get it with an actual month name. Read my above post for an explanation on that.

The amount of time it takes to do it with timestamp is miniscule with the amount of time it will take someone to rewrite it later on.

Ace21

phigga

Right, because everyone builds software based on "how the client might change their mind".

Pull "March 2000" out of a dropdown? The next time I suggest a hypothetical situation, I'll be more specific.....sorry!