Date field - not set as type 'date'

[adammc]

Hello again,

I was just reading a post in the archives about someone having problems searching between a range of dates.
The problem apparently was because he didnt have the date field set as 'date'. They recommended that he change the field type to 'date'.

I think I may have the same problem.

My date field is currently - varchar(150)
I am storing dates in the format of - 17-09-2005

If I change the date type to 'date', will I stuff up my database?

I have over 200 entries in the database, will have to change these entries if I wanted to search between a date range?

[dcrombie]

Yes. And you'll probably need to change the format to yyyy-mm-dd.

[adammc]

But I am in Australia and we read the date as:
dd/mm/yyyy ?

[tomda]

But I am in Australia and we read the date as:
dd/mm/yyyy?

Hey mate ;)
Store the date in your database in yyyy-mm-dd format.
Then, just create a small function which returns the format you need.

[dcrombie]

If you're using PostgreSQL then you can set it up to accept dates in the 'sensible' format, but MySQL is a bit backwards and AFAIK only recognises yyyy-mm-dd.

[adammc]

Ok thats sounds cool, thanks for the replies ;)

Is there a way i can write a script to change all the entries to that format or do I have to do it by hand?

[jatar_k]

you could create a conversion script

create a new column with the type date

your script
select the old value
split it on -
put it back together in the new format
insert it into the new column

when it is finished I would check old values against new before deleting/removing the old column

[adammc]

jatar_k,
thanks for the idea :)

Where would I find info on writing a script like that?

[tomda]

I post you one of my function so that you understand how to change date format using a function. Just play with it to get what you need.

function ListDate($var) {
$day=substr($var,8,2); $dig_month=substr($var,5,2);
$year=substr($var,0,4); $hour=substr($var,11,2);
$min=substr($var,14,2); $second=substr($var,17,2);
switch ($dig_month) {
case "1": $month = "Jan."; break;
case "2": $month = "Feb."; break;
case "3": $month = "Mar."; break;
case "4": $month = "Apr."; break;
case "5": $month = "May "; break;
case "6": $month = "Jun."; break;
case "7": $month = "Jul."; break;
case "8": $month = "Aug."; break;
case "9": $month = "Sep."; break;
case "10": $month = "Oct."; break;
case "11": $month = "Nov."; break;
default: $month = "Dec."; break;}
$var = "".$month." ".$day.", ".$year."<br>".$hour.":".$min.":".$second.""; 
return $var;} 

1/ Create a new column in your database using DATE format
2/ Run a select query and retrieve the date in your format
3/ For each row[date], use the function you just made to change the date in your format to a DATE format
4/ Run an insert query to insert the DATE format to the column you have just created
5/ Close each row