Search code examples
sqloraclesql-likesysdate

Find Specific Values using SQL Like Statement

I'm trying to read a file from among file in my table according to today's date. For example, if today is November 21st, I want to show LIST_21-11-2018. Here is my query : 

CREATE TABLE "FILE_LIST" (
"FILENAME" VARCHAR2(40),
"STATUS" CHAR(1 Byte)
);

INSERT INTO "FILE_LIST" ("FILENAME","STATUS") VALUES('LIST_20-11-2018','N');
INSERT INTO "FILE_LIST" ("FILENAME","STATUS") VALUES('LIST_21-11-2018','N');
INSERT INTO "FILE_LIST" ("FILENAME","STATUS") VALUES('LIST_22-11-2018','N');
INSERT INTO "FILE_LIST" ("FILENAME","STATUS") VALUES('LIST_23-11-2018','N');

SELECT FILENAME FROM FILE_LIST WHERE FILENAME LIKE %'TO_CHAR(SYSDATE,'DD-MM-YYYY')'% AND STATUS = 'N'

The LIKE statement doesn't seem to be working. Hoping to know what is my mistake is ....

Solution

  • Try this :

    SELECT FILENAME FROM FILE_LIST WHERE FILENAME LIKE '%' || TO_CHAR(SYSDATE,'DD-MM-YYYY') || '%' AND STATUS = 'N';