Wednesday, October 29, 2014

Cognos: to_char functionality without using to_char

Some times to_char function may not work in cognos to change the date format into required format

for example cognos by default shows the oracle date into yyyy-mm-dd format by I need to show the date in mm-dd-yyyy format. for that use the below expression..

CASE 
WHEN EXTRACT(MONTH, [Date Field]) < 10
THEN
'0' || 
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(2))
END

|| '-' ||

CASE 
WHEN EXTRACT(DAY, [Date Field]) < 10
THEN
'0' || 
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(2))
END


|| '-' || 

CAST(EXTRACT(YEAR, [Date Field]),VARCHAR(4))