Comparing Dates AND Times Together

Last Post 10 Jul 2006 02:39 PM by ColdFusionFan. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ColdFusionFan
New Member
New Member

--
07 Jul 2006 03:53 PM
I can't believe there is no straightforward way to do this!

I have 2 tables, A and B. Each generates a smalldatetime field with getdate(), DateA and DateB.

First I run QueryA, to get the record I want from TableA. Then I want the LAST TableB record BEFORE the TableA record. What I need is a way of expressing "IsEarlierThan", which compares the date and time together, not in separate parts.

The logic (not the code!) is like this:

(I'll put #PoundSigns# around the external variable, because that's what we do in ColdFusion.)

SELECT Top 1 *
FROM TableB
WHERE DateB IsEarlierThan '#QueryA.DateA#'
ORDER BY DateB DESC

What's the code for that WHERE clause?

Thanks!
nosepicker
New Member
New Member

--
07 Jul 2006 04:33 PM
Try this:

SELECT * FROM TableB
WHERE DateB =
(SELECT MAX(DateB) FROM TableB
WHERE DateB < (SELECT DateA FROM TableA WHERE ...) AS QueryA) AS QueryB


ColdFusionFan
New Member
New Member

--
10 Jul 2006 02:39 PM
nosepicker,

It works!

Pick one for me. ;-)

ColdFusionFan


Acceptable Use Policy
---