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))
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))