Date Comparison 2

[toekneel]

I am trying to do a date comparison and am getting messages indicating that I have incorrect syntax near the '>'.

What I'm trying to do is compare a date in a record to dates in a rate table in order to indicate when a date fits the date for the rate from the table. The rate table has a start date for each rate and a finish date field (which is null if the rate is current). I've tried a few different ways without success. What I'm attempting currently is within a sproc which creates a temporary table for reporting purposes. I'm trying to compare the record date to the start date as one field, then trying to compare the record date to the end date as a second field. My intent would then be to run a summary view on this temporary table and include only records where the first field is true and the second field is either null or true.

Here's my basic select statement (eliminating non-related fields for simplicity sake):

insert into dbo.tmpContractSum ([dtmWeekEndDate], [dtmRateStartDate], [dtmRateEndDate],[dtmDateCompare1], [dtmDateCompare2])
select [dtmWeekEndDate], [dtmRateStartDate], [dtmRateEndDate], [dtmWeekEndDate]>=[dtmRateStartDate] as [dtmDateCompare1], [dtmWeekEndDate]<[dtmRateEndDate] as [dtmDateCompare2]
from qryLaborRates
where dtmWeekEndDate between @WeekEndBegin and @WeekEndConclude

 

[SQLBill]

First, you can't use an evaluation operator (>=) in a SELECT. A SELECT just pulls data and doesn't evaluate it. The >= is used in a WHERE clause. So try nesting a WHERE.

insert into dbo.tmpContractSum ([dtmWeekEndDate], [dtmRateStartDate], [dtmRateEndDate],[dtmDateCompare1], [dtmDateCompare2])
select [dtmWeekEndDate], [dtmRateStartDate], [dtmRateEndDate], (select [dtmWeekEndDate] from qryLaborRates where [dtmWeekEndDate]>=[dtmRateStartDate]) as [dtmDateCompare1], [dtmWeekEndDate]<[dtmRateEndDate] as [dtmDateCompare2]
from qryLaborRates
where dtmWeekEndDate between @WeekEndBegin and @WeekEndConclude


-SQLBill

 

[TheGreenOne]

Another way would be to replace this:
[dtmWeekEndDate]>=[dtmRateStartDate] as [dtmDateCompare1],
[dtmWeekEndDate]<[dtmRateEndDate] as [dtmDateCompare2]

With this:
Case When [dtmWeekEndDate]>=[dtmRateStartDate] Then 'true'
Else 'false' End as [dtmDateCompare1],
Case When [dtmWeekEndDate]<[dtmRateEndDate] Then 'true'
Else 'false' End as [dtmDateCompare2]

 

[toekneel]

The solution posed by SQLBill didn't work because it actually filters the results and I don't want that at this level.

The solution posed by TheGreenOne provides the comparison that is needed here without filtering the data at this level.

Thanks for the help and the input! I learned something important from each point!

Tony
ABQ, NM