Re: Problem with TO_DATE(TO_CHAR(sysdate,'hh24:mi:ss'),'hh24:mi:ss')

On Jan 12, 9:53 am, "Pankaj" <pg_gupt..._at_rediffmail.com> wrote:
> Hi There,
>
> I am developing a procedure that require to increase the counter by 1
> if any is inserted into
> a table after 1 PM. For this i create a column cutoff in a temp table
> and wrote a condition
> like this
> IF ( (TO_DATE(TO_CHAR(CutOff,'hh24:mi:ss'),'hh24:mi:ss')
> < TO_DATE(TO_CHAR(Sysdate,'hh24:mi:ss'),'hh24:mi:ss') )
> )
> THEN
> tTransactionDate := tTransactionDate + 1;
> End If ;
> The cutoff is a date field maintained as '01/03/2006 1:00:00 PM'.
>
> This condition was working fine...but yesterday one user did a
> transaction ar exactly 12 midnight,
> ans system didn't increate the counter.
> Can you tell me why system didn't increase the txn Or suggest some
> better solution?
>
> Thanks,
> Pan

Looks like you need to check whether the hour is really stored as 13 instead of 1, as you are mixing 12h and 24h notation. Looks like the most logical explanation, and something which I would have checked rigthaway.
Apart from that your are code is obviously unnecessarily complicated as
to_number(to_char(sysdate,'hh24') > 13
would have sufficed.

-- 
Sybrand Bakker
Senior Oracle DBA