brainchime.com Latest Questions

Admin
Asked: 2024-10-06T13:32:27+05:30 In:

How to use OPNQRYF to perform a substring on a date field?

I have a file with having date field and I want to apply a substring on the date field. I can do this in SQL using the SUBSTR function very easily. How can I do this using OPNQRYF?

control-language

Related Questions

Leave an answer

Leave an answer

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

1 Answer

  1. Admin

    To perform a substring on a date field, you will first have to cast the field to a string. This can be done using %char.

    The below example will convert the date field to ISO DATEFMT first and then cast it to the character. After that, the substring (%sst) function is applied.

    %sst(%char(datefield "ISO") 1 4) *EQ '1980'

    Now use it with OPNQRYF

    OPNQRYF
    FILE(libname/filename)
    QRYSLT((%sst(%char(datefield "ISO") 1 4) *EQ '1980'))