query using date stored as varchar?

[mdemetri2]

Hello there

 

Hoping someone might be able to steer me in the right direction here. 

 

I have a varchar field that is used to record a date stored in format Y-m-d H:i:s (e.g. 2013-08-07 09:20:46)

 

I would like to run a script that means I can query this field, and allow me to do a between date a and b. 

 

Is this possible due to it being stored as a varchar and in such a format?

 

Many thanks

mdemetri2

[gristoi]

why can you not amend the field type to a dateTime?

[mdemetri2]

thanks for the reply gristoi, I am unable to change the field type at this time but if that is the only way then I suppose I will have to at some stage.

 

I thought there might be a way round it though.

[Barand]

Make sure the date range limits are in the format 'yyyy-mm-dd'. To compare just the date portion of the date/time use DATE() function.

 

eg

$a = '2013-10-20';
$b = '2013-10-23'

$sql = "SELECT something FROM mytable WHERE DATE(mydate) BETWEEN '$a' AND '$b' ";

[mdemetri2]

Barand, as usual quick response, I will certainly give that a try,.....will let you know how things go.

 

thanks!

[mdemetri2]

Barand - thank you, that worked perfectly for what I required. Appreciated as always.

 

thanks!