Hello,
I'm trying to create a sql query where the user enters a value on the prompt and then combine that with a constant value so it becomes the searchable 'variable' in the table on a specific column.
I keep running into problems. The column is a timestamp which includes the date and the time which contains milliseconds to 6 positions plus the AM or PM sign. Like this ' 2017/02/02 5:14:36.705000 PM ' when you look at the rows directly.
What I want is have the user enter two dates and then have the time set as a specific constant value. This way the user only has to enter the dates.
I tried this and I can enter the date but it's not working when I try to 'combine' the prompt value to the static value....
select *
from CRAK_LTCP_TXN_PAYMENT
where
to_char(Payment_timestamp, 'YYYY-MM-DD') = '&&STARTDATE'
and
to_char(Payment_timestamp, 'YYYY-MM-DD')='&&ENDDATE'
and
Payment_timestamp between '&&STARTDATE' + ' 01:00:00:000000 PM'
and '&&ENDDATE'+ ' 09:00:00:000000 PM' ;
Thanks PN