A php- mysql problem- date formats problem

string to date converstion

[AjiNIMC]

Hi all,

I am having a small problem, I am entering the date to mysql database as date('F j, Y, g:i a') but in string format. The database field type for date is varchar(255).

Now I want to search from a particular date(mm/dd/yyyy) to a particular date(mm/dd/yyyy), how will I do that?

let me explain again

Enter to mysql
$enterdate=date('F j, Y, g:i a');
insert into datetable(DateFLD, NameFLD) values ($enterdate,'$_POST[FirstName]');
Here the field DateFLD is varchar(255)type.

Problem
Search between mm/dd/yyyy and mm/dd/yyyy

Thanks
Aji

[coopster]

Why not store the DateFLD column as type

date

rather than

varchar

?

[AjiNIMC]

Now I donot want to take any risks as we get around 3 to 4 registration every one min. Is there any other way?

[lasko]

As coopster says if you make DateFLD as a 'date' feild you will then see how easy it is to use.

We all learn the hard way by trying to search dates in VARCHAR. The setup of your database is the most critical part and using Varchar for dates is wrong.

You may as well do the work now and convert other wise it will be harder later.

When I first started I stored dates like this dd/mm/yyyy in VARCHAR so I had to export the database into CSV then change the whole column into yyyy/mm/dd using Excel and then import back to MYSQL after changing my column to date.

Of course now when entering data into the database you need to make sure the data is inserted correctly for example you must insert as yyyy/mm/dd and not dd/mm/yyyy.

You will then find searching for dates before, after, between and calculating how many days etc is a breeze.

Well worth your time looking into it.

Hope it helps.

[AjiNIMC]

Thanks for the input and I will be using the time format in table, that will be easier and helpful in long run.

Thanks once again
Aji