[SOLVED] Selecting data between two dates

[wiqi]

hey there guys,

I want your help. I want to select data between two date (suppose 12-3-2009 to 4-2-2010).

I have converted the strings to date variables.

table's variable is $tbl, starting date's $sdate and ending date is $edate.

Please reply me with some query to solve this jigsaw out.

 

[Mchl]

Are columns in the table DATE datatype?

[wiqi]

its varchar

(`adate` varchar(25) NOT NULL default '', )

 

but i was inserting date in it using

$adate=date("d/m/y h:i:s");

 

should i change composition on table? also what query would work after changing the data type of  adate column

thanks for your time.

[Mchl]

Certianly. Change the datatype to datetime as it is most fit for storing... date with time.

 

See here:

 

http://www.phpfreaks.com/tutorial/working-with-dates-in-php

[PFMaBiSmAd]

And since you can only do greater-than/less-than date comparisons when the field making up the date are ordered left-right, MDS (most significant digit - year) to LDS (least sugnficant digit - day), the only way you will accomplish what you are trying to do will be to get your dates into a format like YYYY-MM-DD and using a DATE data type is the most efficient way of doing that.

[wiqi]

how would i format these three variable into a sql recognized date format

$mon=$_GET['mon'];

$yr=$_GET['yr'];

$dy=$_GET['dy'];

 

also, the main question remains unsolved that how would i obtain teh data between two given dates

[PFMaBiSmAd]

how would i format these three variable into a sql recognized date format

$a_date = "$yr-$mon-$dy";

 

the main question remains...

$query = "SELECT * FROM your_table WHERE your_date_column BETWEEN '$sdate' AND '$edate'";

 

BETWEEN is inclusive of the end points. If you want to exclude one of both end point(s), you would need to use greater-than/less-than comparison operators.

 

[wiqi]

thank you very much man.. problem resolved. thanks alot once again