Search code examples
sqldatenullwhere-clausesqlanywhere

SQL Select - optional date check in where clause

I have a view in sqlanywhere11 with the following structure (dates in dd-mm-yyyy format):

ID, VALID_FROM, VALID_UNTIL, SOME_VALUE
1, 01-01-2013, 01-02-2013 1
1, 02-02-2013, 01-03-2013 2
1, 02-03-2013, 01-04-2013 3
1, null, null 3

So there are multiple entries with the same id but different dates, and dates may also be null. Now I need a query that selects only the entry with the current date between VALID_FROM and VALID_UNTIL or the one with the null date if the current date is not in any of the valid ranges.
I tried the following query:

SELECT * FROM MYVIEW WHERE VALID_FROM IS NULL OR getdate() BETWEEN VALID_FROM AND VALID_UNTIL;

But this will not work because if there is a null row, it will always be selected, even if there is another row with a valid date range.

Is it possible to create a SELECT query that finds the right rows?

Solution

  • Can't you just do a [SELECT TOP 1] and order it so the null would be last?

    SELECT TOP 1 * 
FROM MYVIEW 
WHERE VALID_FROM IS NULL OR getdate() BETWEEN VALID_FROM AND VALID_UNTIL
ORDER BY CASE WHEN VALID_FROM IS NOT NULL THEN 1 ELSE 2 END