Sometimes, meddling with datatypes and formats can be frustrating. Especially when there are multiple ways to do one task, but still trying to do in an elegant way.
My client wanted the timestamp column without minutes and seconds.
Eg: '07/25/2008 11:45:23' as '07/25/2008 11:00:00'
Unfortunately, forums and documentation did not help much :(
Many thanks to DBA Girija who helped me get it the 'elegant' way :)
type casting to character, concatenating and converting back to timestamp:
, CAST((CAST(CURRENT_TIMESTAMP(0) AS CHAR(10))
||' '||CAST(EXTRACT(HOUR FROM TIME) AS CHAR(2))
||':00:00') AS TIMESTAMP(0));
usage of interval function and adding it to date. Usage of interval function for this purpose was a bit tricky. Hope this posts helps people facing similar problems
,CAST(current_date AS TIMESTAMP(0))
+ CAST(EXTRACT(HOUR FROM current_timestamp)
AS INTERVAL HOUR(2));