Home » SQL & PL/SQL » SQL & PL/SQL » Date conversion
Date conversion [message #337343] Wed, 30 July 2008 10:54 Go to next message
nandinir
Messages: 9
Registered: July 2008
Junior Member
This query works fine if period is a date column. What if its a number column, how can I type cast it?

SELECT
     count_new_accts,
     acct_id
FROM
    cc_tab
where
          period between add_months(trunc(sysdate,'MM'),-1)
          and trunc(sysdate,'MM') - (1/86400)
          group by acct_id


I did the following, but I'm not sure if its correct as it still throws an error.

11:51:54 AM  ORA-00932: inconsistent datatypes: expected NUMBER got DATE 

where
period between to_date(add_months(trunc(sysdate,'MM'),-1))
          and to_date(trunc(sysdate,'MM') - (1/86400))
          group by acct_id


I know its very simple but I'm very bad at typecasting. Can someone please help me with this.

Thanks,
Nandini
Re: Date conversion [message #337346 is a reply to message #337343] Wed, 30 July 2008 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could you cast a number to a date? What could be the rule to convert one to the other?
If you know it, then use TO_DATE.

Regards
Michel
Re: Date conversion [message #337347 is a reply to message #337343] Wed, 30 July 2008 11:23 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>Can someone please help me with this.
visit http://tahiti.oracle.com
& read up on TO_DATE(), TO_CHAR() & TO_NUMBER() functions
Previous Topic: ORA-30625: method dispatch on NULL SELF argument is disallowed
Next Topic: Error : Invalid ROWID
Goto Forum:
  


Current Time: Sat Dec 03 18:06:18 CST 2016

Total time taken to generate the page: 0.11394 seconds