SQL >= and <= in an IIF or WHERE getting an error

Question

I have a crossjoin between two tables where I need the PayrollOID for a set of conditions. I have tried doing this in a CASE statement, CASE and IIF statements in the WHERE clause, now in the regular code realizing I will have to do another pass to get rid of empties.

I have Job Approvals that go to payroll based on the date of approval. We are changing that to the date the approval is entered.

If the Apr_Entered is >= JobPayStart and Apr_Entered <= TimesheetsDue and JobEndDate >= JobPayStart and JobEndDate <=JobPayEnd - give me that OID. Don't look for anything else.

Else - I want the job to be paid where the Apr_Entered is >= JobPayStart and Apr_Entered <= JobPayEnd.

I am getting an error: Error in list of function arguments: '>' not recognized. Unable to parse query text.

But it does run. I am however getting duplicates where both brackets are being shown for an overlap job.

I need help tightening my bookends up - so when first is found get out. I also need to know why I am still getting the error when I have converted datetime fields to Date for comparison.

IIF(CONVERT(varchar(10), ProtoPayroll.AprEntered,110) >= CONVERT(varchar(10), PayDates.PayStartDate,110)
AND CONVERT(varchar(10), ProtoPayroll.AprEntered,110) <= CONVERT(varchar(10), PayDates.TimesheetsDue,110)
AND CONVERT(varchar(10), ProtoPayroll.JobEndDate,110) >= CONVERT(varchar(10), PayDates.PayStartDate,110)
AND CONVERT(varchar(10), ProtoPayroll.JobEndDate,110) <= CONVERT(varchar(10), PayDates.PayEndDate,110), 
    --First IIF's result
    CONVERT(nvarchar(40), dbo.Paydates.OID),
    --Now another IIF
    IIF (CONVERT(varchar(10), ProtoPayroll.AprEntered,110) >= CONVERT(varchar(10), PayDates.PayStartDate,110)
    AND CONVERT(varchar(10), ProtoPayroll.AprEntered,110) <= CONVERT(varchar(10), PayDates.PayEndDate,110),
    --Second IIF's results
    CONVERT(nvarchar(40), dbo.Paydates.OID), SPACE(40))) AS PayrollOID

Unless you can post a script that reproduces the problem, I don't think anybody can definitively answer this question. — Tab Alleman, Dec 21 '17 at 19:25

Thom A · Answer 1 · 2017-12-21T19:30:38.563

There's a lot of guess work here, but that SQL needs a tidy. I can't see anything wrong with your SQL, "per-say" but you're asking for trouble by coverting dates to a varchar. As an example, in varchar the date '11/11/2016' is AFTER 01/01/2018' Yes, that's right, I said after. That's because 1 > 0 (the first characters of the varchar is used for sorting first, then the second, etc, etc).

Anyway, here's a tidier version of your SQL, using a CASE expression instead. Obviously this is completely untested. What you really need to do is post some Sample data, DDL, and your expected result set. You don't actually ask a question here, you just tell us your aims and say your getting an error (which we can't replicate).

SELECT CASE WHEN PP.AprEntered >= PD.PayStartDate
             AND PP.AprEntered <= PD.TimesheetsDue
             AND PP.JobEndDate >= PD.PayStartDate
             AND PP.JobEndDate <= PD.PayEndDate THEN CONVERT(nvarchar(40), PD.OID)
            WHEN PP.AprEntered >= PD.PayStartDate
             AND PP.AprEntered <= PD.PayEndDate THEN CONVERT(nvarchar(40), PD.OID)
            ELSE SPACE(40)
       END AS YourColumn
FROM ProtoPayroll PP 
     JOIN PayDates PD ON --JOIN Criteria
WHERE ...;

If this isn't helpful to you, have a look at How to ask

@TabAlleman Yes you were correct, however, it's also helpful to state why it's wrong; not just say "this is wrong". I have fixed the problem, which was the missing initial `WHEN`. — Thom A, Dec 21 '17 at 19:27
I couldn't tell exactly what was wrong because I wasn't sure if you meant to use a nested CASE expression or not. — Tab Alleman, Dec 21 '17 at 19:28

SQL >= and <= in an IIF or WHERE getting an error

1 Answers1