SQL TO_DATE/TO_CHAR query

[ironic100]

I am trying to select records where the parameter date is greater than/equal to a record in PA_TRANS. I want to have a date and time comparison to the records in the table. It works for only for the date portion i.e. dd/mmm/rrrr as in below:

SELECT t.SITENO, t.PA_TRANSNO, t.PA_TR_COMM, t.PA_TR_DATE, t.PA_PAYMENT_TYPE, t.USER_NAME
FROM Pa_Trans t
WHERE siteno = 8
AND t.user_name = 'HSOWNER'
AND TO_DATE((TRUNCp_transactions_upto_date)), 'dd/mm/rrrr') >= TO_DATE((TRUNC(t.pa_tr_date)), 'dd/mm/rrrr')


But for the time portion it doesn't. Do I need to TO_CHAR or TO_DATE or anything else?

SELECT t.SITENO, t.PA_TRANSNO, t.PA_TR_COMM, t.PA_TR_DATE, t.PA_PAYMENT_TYPE, t.USER_NAME
FROM Pa_Trans t
WHERE siteno = 8
AND t.user_name = 'HSOWNER'
AND TO_DATEp_transactions_upto_date, 'DD-Mon-YYYY HH24:MI:SS') >= TO_DATE(t.pa_tr_date, 'DD-Mon-YYYY HH24:MI:SS')

 

[nagornyi]

What are the types of p_transactions_upto_date parameter and t.pa_tr_date field, and what are their formats if they are chars? You can not convert the same string to date using both dd/mm/rrrr and DD-Mon-YYYY HH24:MI:SS masks . Otherwise the answer to your questions is that you need to convert both arguments to the date type preserving the time porion of it, then you will be able to compare them.

 

[ironic100]

thanks nagornyi i think i have it. i deleted the second to_date and it seems to work with the parameters being strings.

i'm now trying to concatenate the two together so the user first inputs the date then the time in a seperate box, any ideas?

thanks.

 

[nagornyi]

I think it should be the same. Something like:

...
 TO_DATE(:p_transactions_upto_date||:p_transactions_upto_time,'DD-Mon-YYYY'||'HH24:MI:SS')
...

depending on the format you require users to enter date and time parameters.