big big problem with dates

robsgaming

I have a HUGE problem that I didn't even think of before. I'm inserting dates into mysql as 12-30-2003 10:00:57 in that format.

Is there anyway to globally change all those dates to read as
(Year, Month, Day) 2003-12-30

This is a huge problem because I sort a lot of stuff based on the date. I created my own boards and now sincer it's the new year, NOTHING is showing up since Dec 31 when I sort topics and such. I understand I can sort by ID but I'm doing it by date. Thanks in advance for any help.

leatherback

Eehmm..

what is the fieldtype of the curently stored date?
Perhaps it is wise to insert it as a timestamp?

robsgaming

That's how im going to do it.

But I still need to convert all of the other dates to be Y-m-d instead of M-d-y

get what i'm saying?

I corrected the problem now, but I need to update all of the other records and fix the problem.

leatherback

hi,

I am a bit confused on date etc in mysql.. But if I am correct.. cant you select date in a specific format from the database?

robsgaming

I have no idea. That isn't hte problem tho.

THe problem is that I have dates already inserted in M-D-Y format and I need to convert them all to Y-M-D format

Can I build a script to update all of the records???

leatherback

Again I ask:

what is the fieldtype of your date colum?

robsgaming

Ok, the dates are in the database like this:

Month, Day, Year

12-31-2003

I want to update all of the records and have the dates show up in Year, Month, Day now

2003-12-31

There has to be some script I can write in php to update all of those records, correct? Basically I need to take the last 4 characters, move them to the front and add a '-' and remove the last '-' after the day.

robsgaming

Originally posted by leatherback
Again I ask:

what is the fieldtype of your date colum?

Don't worry about the field type. It's not inserted as timestamp anyways. I know its bad coding... I appreciate any suggestions to IMPROVE how it works now, but what I'm looking to do is what I stated in the post above.

leatherback

It DOES matter.
Do you want some help here?

Anyway. IF you have a text-type field, then you have to query each record, breack it up, and insert it again at a later stage.

Then again.. If you have a timestamp / date / date-time type colum, you may be able to do something with altertable, but maybe all you need to do is change the way you retrieve the info.

robsgaming

If I convert those records to timestamp, it's going to screw up the dates in all of them then I will be screwed really good. I'm planning on coverting it to timestamp(14) but I Need to convert the existing data to that format first don't I?

YYYYMMDDHHMMSS

leatherback

Have you read the date functions of mysql?

People have been thinking about this.

First of all: Make a backup of the database before you run any updates..!

Anyway: THere is a way to select dates from a datecolum in a specified format.
SELECT DATE_FORMAT('1999-01-01', '%d-%m-%y');
should result in:
01-01-1999

Date & time functions

So that is one solution.

As for transforming:

====
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the resulting value is set to '00:00:00', because the DATE value contains no time information.
If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the resulting value is deleted, because the DATE type stores no time information.

Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and will be converted to 0 if assigned to such an object.

source: MySQL datetime