mysql date help

[esthera]

I have a date in the following format : 08/06/2004 00:47:47
stored in a char field. What sql would I do to look between 2 dates. --- say I wanted to select all entries between 08/01/2004 and 08/10/2004.

Will between work if it is not a date field in the sql?

If not -- is there anyway to copy the time field to another time datetime field?

Please advise?

[raf]

selecting on the stringvalues will be (almost) impossible.

If you run MySQL 4.1.1, then converting to dates would be something as easy as

UPDATE yourtable SET yournewcolumn = STR_TO_DATE(yourexistingcolumn,'%m/%d/%Y %H:%i:%s');

(i'massuming you data is now in mm/dd/yyyy hh:mm:ss format)

If you run a lower version, then it'll be something like

UPDATE yourtable SET yournewcolumn = CONCAT(SUBSTRING(yourexistingcolumn,7,4), '-', LEFT(yourexistingcolumn,2), '-', SUBSTRING(yourexistingcolumn,4,2), ' ', SUBSTRING(yourexistingcolumn FROM 12));

[esthera]

Thanks. That seemed to work well.
How can I set the default for future entries to be the datetime of now?

[raf]

In MySQL, you can only set a fixed value as default. Like '2004-01-01 00:00:00' Not a function like Now().

You can however use the Now() inside your insertstatement, which is what i always do.
Like

insert into table (var1, var2, createdatetime) values(1,2,Now())

Or, you could create a column of type timestamp and then the value is automatically set. However, when you update the record, the timestamp is also updated to Now, which is not what you would want if you need the creationtime !

[esthera]

Problem is I don't really want to change the php sending in the values as it is sitting in multiple places.

Is there anyway to do what I originally said and do a select on the dates in the char field?

[raf]

Not realy.

Well,you could select the complete table and then us a strtotime() on the field and dumpin in an array and filter it then with a callback-function, but that's kina crazy.
Another option would be to select the complete table into a temporarely table and do the valueconversion there (likeposted before) and then run the select on that table. But again, this is kinda crazy. specially if your table gets bigger.

<edit>yet another sollution would be to do the valueconversion before the select.
So you'd then do the update from my initial post, but yopu also set an updated-flag. So your inital update + all updates before executing the select would be (assuming that you create an extra field 'isupdated' with default=0':

UPDATE yourtable SET yournewcolumn = STR_TO_DATE(yourexistingcolumn,'%m/%d/%Y %H:%i:%s') WHERE isupdated=0;

this would actually be the most performant sollution. The updates wount take long, there is no extra strain on the webserver and if you would change your inserting php, then you could just removethe update.
</edit>

A sort of hack would be to create a collection of possiblevalues.
In your example (dates between 08/01/2004 and 08/10/2004), that would be
08/01/2004%
08/02/2004%
08/03/2004%
08/04/2004%
08/05/2004%
08/06/2004%
08/07/2004%
08/08/2004%
08/09/2004%
08/10/2004%

so you could then run a query like

select foo from bar where datecolumn LIKE '08/01/2004%' or datecolumn LIKE '08/02/2004%' ...

get the picture? You could dynamically create the where-clause, but this is still a messy sollution.

Fixing your dbdesign (it's bad design that caused this problem) and adapting your code is the only real sollution. Setting up some fix like the ones above will probably take longer then just going through the code and setting it straight.