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 :
- If the date is between 1-15 , it will be converted to last date of that particular month .
- 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 ?