[RESOLVED] SQL Server + Date Issue

[obrienkev]

Hi all. Working on SQL Server. The date is causing me some problems. I want to return rows in the below query but doesn't seem to recognise $class_date.
Why is this??

$query = "SELECT t.ffnumber ".
                  "FROM training t, class c ".
                 "WHERE c.course_ID = '$course' ".
                 "AND c.location_ID = '$location'".
                 "AND c.class_start = '$class_date' ".
                 "AND c.class_ID = t.class_ID";

Echoes as this...
SELECT t.ffnumber FROM training t, class c WHERE c.course_ID = '80' AND c.location_ID = '2'AND c.class_start = '2006-03-27' AND c.class_ID = t.class_ID

Returns 0 results even when all match. Problem is with the date. How do I solve this??

[devinemke]

look carefully. you need a space after $location.

[obrienkev]

didnt make a difference im afraid.

[laserlight]

Could try a:

$query = "SELECT t.ffnumber
	FROM training AS t, class AS c
	WHERE c.course_ID = '$course'
		AND c.location_ID = '$location'
		AND c.class_start = '$class_date'
		AND c.class_ID = t.class_ID";

[Sxooter]

is class_start a date, or a timestamp / datetime type?

A date won't match a timestamp. I.e. you're likely asking is '2006-04-01' == '2006-04-01 12:00:00' and of course, those aren't the same.

Better to use between most times.

Is class_start between '2006-04-01 00:00:00' and '2006-04-01 23:59:59'

Note that some days might technically have a leap second and go to 23:59:60, so I guess that might be an issue but a pretty rare one 🙂

[Roger_Ramjet]

You are falling foul of the date format being used in sql server. Remember, sql server stores tha date as a number and converts for comparison/display. The format used is the server default, either country setting or 100, unless you spcify explicitly in your query with CONVERT(). You are using ANSI format which is const 102; US format is const 101; 100 is server default and in the format mon dd yyyy hh:mm A.M./P.M.