Selecting a DATETIME20 value using a filter in DI Studio

[EinarRoed]

I have opened the following table in DI Studio:

I open the filter and build the following expression:

VALID_FROM_DTTM = "25APR2012:22:58:23"dt

Yet, no rows are returned.

What am I doing wrong?

[LinusH]

Is this a Base SAS table?

Maybe you have microseconds. Apply datetime22.3 format to see any decimals.

Try to this programmatically as well, outside DIS. Both viewing and filtering.

[TomKari]

The constant in your expression is an integer, as it represents a number of seconds. However, it is possible for a datetime value to have decimals, representing parts of seconds, which wouldn't show up in your formatted display.

Try FLOOR(VALID_FROM_DTTM) on the left side of the expression.

Tom

[EinarRoed]

You're both correct. When using the datetime22.3 format the values appear as "25APR2012:22:58:23.365", and I can use FLOOR on the left side to select them regardless of the decimals. Thank you very much.