Calculate Date Range

[sharmams0978]

Hello, 

I have a datetime column that has values such as shown below as a sample

04MAY2018:08:46:20.316999912

11JAN2021:11:07:37.048000097

17MAR2019:00:24:55.558000088

how do I filter the whole table based on the above column date range say between 04MAY2018 AND 17MAR2019? I tried this: WHERE DATE BETWEEN "04MAY2018"d AND "17MAr2019"d but it doesn't work. Please help.. thank you.

[jimbarbour]

---

@sharmams0978 wrote:

Hello, 

I have a datetime column that has values such as shown below as a sample

04MAY2018:08:46:20.316999912

11JAN2021:11:07:37.048000097

17MAR2019:00:24:55.558000088

how do I filter the whole table based on the above column date range say between 04MAY2018 AND 17MAR2019? I tried this: WHERE DATE BETWEEN "04MAY2018"d AND "17MAr2019"d but it doesn't work. Please help.. thank you.

---

Well, you can't compare a Date-Time variable with a Date value.  Date-Time variables are much larger, and it will not be a valid comparison.  If you want to use a date range, the DATEPART function should do the trick.  Something like this:

DATA Filtered_Data;
    SET Unfiltered_Data;

     IF  '04MAY2018'd < DATEPART(Datetime_Column) < '17MAR2019'd;
RUN;

Jim

[s_lassen · Accepted solution]

The problem is that your values are datetimes, not dates. Unlike e.g. T-SQL, where you can compare dates and datetimes directly, you will either have to convert the datetimes in the table to dates (using the DATEPART function, as suggested by @jimbarbour ) or use datetime constants in your comparison. I would go with the last solution, as it executes faster (datepart does an extra calculation for every single row in the table):

proc sql;
  select * from have where
  datetime_column>='04may2018:00:00:00'dt and
  datetime_column<'18mar2019:00:00:00'dt
;

Note that I changed the BETWEEN construct, in order to get exactly the same result as you would with BETWEEN on the dateparts and the dates you mentioned.