Include null values within a date range

Question

Im trying to filter out the data between the date range, which also includes null values, eg: start_date = 18/01/2013 and end_date = 20/01/2013

In some data in the database the start_date is 19/01/2013 and end_date = null; I need to include this particular value as well, Here's the query I've tried so far

SELECT T1.START_DATE,
       T1.END_DATE
FROM   myTable T1
WHERE  to_date(T1.START_DATE, 'DD/MM/yyyy HH24:MI:SS') >= to_date('01/01/2013 10:58:58', 'DD/MM/yyyy HH24:MI:SS')
       AND to_date(T1.END_DATE, 'DD/MM/yyyy HH24:MI:SS') <= to_date(IS NULL('21/01/2013 10:58:58', 0), 'DD/MM/yyyy HH24:MI:SS')

I still can't find the results with null values, Please let me know how will i go about solving this issue.

Thanks

`TO_DATE()` isn't part of SQL Server, so far as I'm aware. Your sample code and your tags don't match, but I'm not sure in what direction it needs correcting. — Damien_The_Unbeliever, Jan 22 '13 at 07:53
I can't find any reference to `to_date()` in MSDN either, but is the query currently returning the records where the END_DATE IS NOT NULL? — rink.attendant.6, Jan 22 '13 at 07:55
In a comment to a deleted answer, you said the version is `1.5.5`. That's *not* a SQL Server version number. Either it's the version number of a non-standard client tool that you're using to access SQL Server, or (more likely) you're not using SQL Server. If that's the case, please delete the `sql-server` tag and add a tag that matches the database system you *are* using. — Damien_The_Unbeliever, Jan 22 '13 at 07:59

rink.attendant.6 · Answer 1 · 2013-01-22T07:58:38.207

I'm not completely familiar with SQL server, but it doesn't look like to_date() is a SQL Server function anyways. I found some reference of it in the Oracle Database docs, so maybe you mean Oracle SQL. try this:

SELECT
    T1.START_DATE,
    T1.END_DATE
FROM myTable T1
WHERE 
    to_date(T1.START_DATE,'DD/MM/yyyy HH24:MI:SS') >= to_date('01/01/2013 10:58:58','DD/MM/yyyy HH24:MI:SS')
    AND (
        to_date(T1.END_DATE,'DD/MM/yyyy HH24:MI:SS') <= to_date('21/01/2013 10:58:58', 'DD/MM/yyyy HH24:MI:SS')
        OR T1.END_DATE IS NULL
    );

Include null values within a date range

1 Answers1