I have a scenario, where I am using two variables one for data and one for time.
I want to concatenate it and use the concatenated value for filtering purpose on load_dt_tm column in my select query
The variable value is set like this
Code: Select all
\set
PROC_DT = ''20130501''
PREV_PROC_DT = ''20130430''
PROC_TM = ''235959''
PREV_PROC_TM = ''000000''
Now, I want to compare load_dt_tm(timestamp) column to a PREV_PROC_DT+PREV_PROC_TM value
Code: Select all
select * from test where
load_dt_tm > (:PREV_PROC_DT::VARCHAR(15)::DATE||' '||CAST(:PREV_PROC_TM as timestamp))
And also, if the PREV_PROC_TM = ''000000'' then it takes the value as 0.
I have found the workaround, but its too much tedious to be used
Code: Select all
select * from test where
load_dt_tm >
(:PREV_PROC_DT::VARCHAR(15)::DATE||' '||
((CASE WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 5 THEN '0' || :PREV_PROC_TM
WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 4 THEN '00' || (:PREV_PROC_TM :: VARCHAR(6))
WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 3 THEN '000' || (:PREV_PROC_TM :: VARCHAR(6))
WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 2 THEN '0000' || (:PREV_PROC_TM :: VARCHAR(6))
WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 1 THEN '00000' || (:PREV_PROC_TM :: VARCHAR(6))
WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 0 THEN '000000' || (:PREV_PROC_TM :: VARCHAR(6))
ELSE (:PREV_PROC_TM :: VARCHAR(6))
END ) ::VARCHAR(15)::TIME))::TIMESTAMP