Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with TO_DATE(TO_CHAR(sysdate,'hh24:mi:ss'),'hh24:mi:ss')

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

From: sybrandb <sybrandb_at_gmail.com>
Date: 12 Jan 2007 01:26:45 -0800
Message-ID: <1168594005.129653.291850@a75g2000cwd.googlegroups.com>

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
Received on Fri Jan 12 2007 - 03:26:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US