Page 1 of 1

converting text to date

Posted: Wed Feb 15, 2006 2:35 am
by tamiya
Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Hi,

I have a problem concerning a certain field in our database. When they created the table years ago, they made the field for the publication date a varchar format, instead of any date or datetime. The way info is put in is 
01JAN05. Now i need to extract informationa ccording to this date, but the date is een as a text string, which makes my information wrong.

I dont know if STR_TO_DATE will really work for me, but if it will, how do i need to use it?

An example of the query (which does not produce the correct results):

Code: Select all

$cfromdately1 = mktime(0, 0, 0, date("m")  , 1, date("Y")-1);
$cfromdately1 = str_replace("-","",date("d-M-y",$cfromdately1));
$ctodately2 = $cfromdately1;

Code: Select all

$sb2 = "select * from project.cprojinc where PDATE >= '$cfromdate2' AND PDATE < '$ctodate2' AND STARTED != 'Y'";
//echo "$sb2<br>";
$mb2 = mysql_query($sb2) or die("sb2 dies<br>$sb2");
$nb2 = mysql_num_rows($mb2);
**Note: PDATE = VARCHAR(7)

Please Advise


Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Converter

Posted: Wed Feb 15, 2006 3:37 am
by ryos
Let me see if I've got this right: you need to select something based on a date range, but the dates are in strings instead of proper dates, right?

I'm pretty sure that doing a > comparison on a varchar will do a lexicographical comparison. That's not what you want.

How big is this database? Is it small enough that selecting everything and using PHP to filter it is an option? If not, you'd probably need an ugly kludge of queries that select all rows containing the months and years in the range, then using PHP to chop them (for example, to select 07JAN05 - 15MAR05, you'd have to select from JAN05, FEB05, and MAR05, then filter the extras off).

And if changing the column type is at all feasible, by all means do that.

Posted: Wed Feb 15, 2006 4:01 am
by tamiya
Unfortunately, there are a few probelsm preventing me from doing anything easy...

Currently, the table alone is 57700 + rows strong with quite a few columns as well
There are numerous programs reliant on this particular table, so changing the format is out of the question
And doing that whole string of queries and such is actually more time consuming than what the actual eventual output of all of this is worth...
We've even considered making a tempory/extra table with just these dates, and then writing a script to input data into this new table, after being formatted. but again, not really feasible....

We do however, get this information somehow, using a telnet system (last updated '96 - SHOCKING), as our ladies can print a report on the information i want to display... I think that particular program was written in fox or something very alien to me.

I have just started viewing this code, so i will see if i can make any heads or tails out of it, before i can proceed.

Thanks, Tamiya