Filtering based on the month someone was born-CodePudding

Currently trying to create a query that filters on the month that someone was born but its returning errors. I want it to display everyone who was born on 05 month.

SELECT "Person Names"."Full Name" saw_0,
       "Person"."Person Date Of Birth" saw_1  
  FROM "Workforce Management - Person Real Time"
 WHERE "Person"."Person Date Of Birth" = date '%-05-%'

CodePudding user response：

Considering "Person"."Person Date Of Birth" is of type DATE you could simply do:

WHERE TO_CHAR("Person"."Person Date Of Birth",'MM') = '05'

CodePudding user response：

DATE followed by a string is a date literal. The string must have the format yyyy-mm-dd. The expression date '%-05-%' is hence syntactically incorrect.

You can, however, extract a month from a date:

WHERE EXTRACT(MONTH FROM "Person"."Person Date Of Birth") = 5

CodePudding user response：

You can use EXTRACT to check the month. In case your column is already of type date, this will do:

SELECT * FROM yourtable WHERE EXTRACT(MONTH FROM datecolumn) = 5;

If the column is of type varchar, you can cast it as date and then do the same:

SELECT * FROM yourtable WHERE 
EXTRACT(MONTH FROM TO_DATE(varcharcolumn,'dd.mm.yyyy')) = 5;

Please see a simple example here: db<>fiddle