WHERE statement in PROC SQL to filter a date from a column formatted as datetime20

Question

I have a column formatted as datetime20. and need all the data whenever this date is 07JUL2023, but I'm having some trouble writing it.

Does anyone know any way to do it? This is probably a newbie question, but I'm still learning

Thanks in advance

I tried everything I could think of and found on google, format, datepart, cast... but nothing seems to work

Answer 1

First, make sure your column is a SAS datetime. If it is, you can convert it to a date and use a date literal: '07JUL2023'd

proc sql;
    select *
    from have
    where datepart(datetime) = '07JUL2023'd
    ;
quit;

Or use a datetime literal: '07JUL2023:00:00'dt

proc sql;
    select *
    from have
    where datetime between '07JUL2023:00:00'dt and '07JUL2023:23:59'dt
    ;
quit;

Otherwise, if it's a string, you need to convert it to a SAS datetime with the input function.

proc sql;
    select *
    from have
    where datepart(input(datetime, datetime20.)) = '07JUL2023'd
    ;
quit;