Filtering out Dates.......

[fenneraj]

Hi,
I am using Cold Fusion to connect to an access DB. I am trying to get the SQL to filter out dates that are less than my threshold date (18/02/2004). The dates in the DB are listed as dd/mm/yyyy hh:mm:ss with the data type set to General Date. When i pull in results using this query:

SELECT *
FROM tbl_Xtra_MED INNER JOIN tbl_Inst_Posn
ON tbl_Xtra_MED.Client_Number = inst_Posn.Client_Number
WHERE DONE <> Nf_Items
AND tbl_Xtra_MED.Last_Update < 18/02/2004

I get no results returned from this query, yet taking out the last WHERE condition yields me with 310 results. If i use ' ' around the date, i get a 'data type mismatch' error. I have also tried putting 18/02/2004 00:00:00 in the condition to try and match the data in the DB. This provides me with a syntax error.

Any help is much appreciated

Regards
Andrew Fenner

 

[grega]

What database? If Oracle,

SELECT *
FROM tbl_Xtra_MED INNER JOIN tbl_Inst_Posn
ON tbl_Xtra_MED.Client_Number = inst_Posn.Client_Number
WHERE DONE <> Nf_Items
AND tbl_Xtra_MED.Last_Update < to_date('18/02/2004','DD/MM/YYYY')

Greg.

 

[PHV]

Have you tried #18/02/2004# ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fenneraj]

Sorry Guys, Neither of those worked. I am using an MS Access DB with a Cold Fusion frontend.

 

[Mercury2]

Try out this. That should work on MS.Access DB

... AND tbl_Xtra_MED.Last_Update < DateValue(#18/02/2004#)

HTH -- Philippe