#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Date Query with NULLs


    In the following example, only the last 'where' clause produces the required results. Shouldn't all three where clauses produce the same results (return both rows)?

    Code:
    select *
    from (
      select 1 as ID, Date 'Sep 18, 2012' as DateStart, Date 'Dec 18, 2012' as DateEnd from RDB$DATABASE
      union select 2 as ID, cast(null as Date) as DateStart, Date 'Jan 18, 2013' as DateEnd from RDB$DATABASE
    ) as Test
    --where 'TODAY' between Coalesce(DateStart, 'TODAY') and Coalesce(DateEnd, 'TODAY')
    --where Coalesce(DateStart, 'TODAY') >= DateStart and Coalesce(DateEnd, 'TODAY') <= DateEnd
    where (DateStart is null or 'TODAY' >= DateStart) and (DateEnd is null or 'TODAY' <= DateEnd)
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Actually, just modified the first 'where' clause to this, and it works:
    Code:
    where Date 'TODAY' between Coalesce(DateStart, 'TODAY') and Coalesce(DateEnd, 'TODAY')
    Originally Posted by DwightM
    In the following example, only the last 'where' clause produces the required results. Shouldn't all three where clauses produce the same results (return both rows)?

    Code:
    select *
    from (
      select 1 as ID, Date 'Sep 18, 2012' as DateStart, Date 'Dec 18, 2012' as DateEnd from RDB$DATABASE
      union select 2 as ID, cast(null as Date) as DateStart, Date 'Jan 18, 2013' as DateEnd from RDB$DATABASE
    ) as Test
    --where 'TODAY' between Coalesce(DateStart, 'TODAY') and Coalesce(DateEnd, 'TODAY')
    --where Coalesce(DateStart, 'TODAY') >= DateStart and Coalesce(DateEnd, 'TODAY') <= DateEnd
    where (DateStart is null or 'TODAY' >= DateStart) and (DateEnd is null or 'TODAY' <= DateEnd)

IMN logo majestic logo threadwatch logo seochat tools logo