teradata query to analyse a particular DOB and convert it to max date of that particular month

33 Views Asked by At

I want to build a teradata query that will analyse the DOB field of a table and use case statement to update accordingly. It will keep the month and year fields as it is and change the date as per below rule :

  1. If the date is between 1-15 , it will be converted to last date of that particular month .
  2. If the date is between 16-31 , it will be converted to first date of that particular month .

For an example, 02/13/2000 will be transformed to 02/29/2000 and 03/31/1993 will become 03/01/1993 .

I have tried below query:

update Table_1 
From Table_2
    set DOB =
        CASE
            WHEN EXTRACT(DAY FROM DOB)BETWEEN 1 AND 15 THEN
                LAST_DAY(DOB)
            WHEN EXTRACT(DAY FROM DOB)BETWEEN 16 AND 31 THEN
                DATE(EXTRACT(YEAR FROM DOB)||'-'||EXTRACT(MONTH FROM DOB)||'-01')
            ELSE DOB
        END
WHERE Table_1.ID= Table_2.ID;

but keep getting error like "update failed. expected something like an 'end' keyword between the 'date' keyword and '(' " , however I can't find the syntactical error.

can anyone please help to modify the query ?

0

There are 0 best solutions below