Re: DatabaseError: ORA-01843: not a valid month

Never allow Oracle to implicitly convert a string to a date! Always use 
an explicit date conversion function, such as TO_DATE or TO_TIMESTAMP, 
with a valid date format string as the second parameter to the function 
call. See, e.g., 
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

cursor.execute("select to_char(sysdate, 'yyyy-mm-dd') from dual")
cursor.execute("select to_char(to_date(:1, 'yyyy-mm-dd'), 'yyyy-mm-dd') 
from dual", ['2013-04-22',])
cursor.execute("select to_char(:1,' yyyy-mm-dd') from dual", 
[datetime.datetime.now(),])

When using cx_Oracle, you may pass data values of type datetime.datetime 
as parameters to the cursor execute functions. These are used in the SQL 
statement as values of type DATE. In that case, you perform the 
conversion from string to date within your Python code.

cursor.execute("select dump(:1, 1016) from dual", 
[datetime.datetime.now(),])

Setting the NLS_DATE_FORMAT and similar parameters are a very good 
technique for controlling the implicit conversion of date type values to 
strings. When the resultant strings are used for any purpose other than 
display, explicit conversion is, however, much more reliable on the long 
term. These parameters are also convenient when using implicit 
conversions with interactive input, but those implicit conversions 
should never make their way into non-interactive code.

- Doug


On 2013-04-22 11:42, Joel Slowik wrote:
>
> What does your code look like? Do you know what your NLS setting is 
> for your session? You might need to set it yourself before passing the 
> dates around.
>
> -joel
>
> *From:*Andrey Nikolaev [mailto:[email protected]]
> *Sent:* Sunday, April 21, 2013 6:51 PM
> *To:* cx-oracle-users
> *Subject:* Re: [cx-oracle-users] DatabaseError: ORA-01843: not a valid 
> month
>
> Hello,
>
> I just started using cx_Oracle and came across an issue related to 
> Oracle Date datatype. I am getting an error ORA-01843 every time I 
> need to do anything with dates.
>
>   
>
> A similar problem is described on the stackoverflow: 
> http://stackoverflow.com/questions/15396241/cx-oracle-ora-01843-not-a-valid-month-with-unicode-parameter
>
> Has anybody come across this issue? I believe something is wrong with 
> how I use cx_Oracle but I can't find out what. I tried the same case 
> as in the stackoverflow post and got the same error.
>
>
> --
> Thanks,
> Andrey Nikolaev                            
> mailto:[email protected] <mailto:[email protected]>
>
> ------------------------------------------------------------------------
> Confidentiality Note: This electronic message transmission is intended 
> only for the person or entity to which it is addressed and may contain 
> information that is privileged, confidential or otherwise protected 
> from disclosure. If you have received this transmission, but are not 
> the intended recipient, you are hereby notified that any disclosure, 
> copying, distribution or use of the contents of this information is 
> strictly prohibited. If you have received this e-mail in error, please 
> contact Continuum Performance Systems at {203.245.5000} and delete and 
> destroy the original message and all copies.
>
>
> ------------------------------------------------------------------------------
> Precog is a next-generation analytics platform capable of advanced
> analytics on semi-structured data. The platform includes APIs for building
> apps and a phenomenal toolset for data science. Developers can use
> our toolset for easy data analysis & visualization. Get a free account!
> http://www2.precog.com/precogplatform/slashdotnewsletter
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


-- 
Doug Henderson, Calgary, Alberta, Canada

------------------------------------------------------------------------------
Precog is a next-generation analytics platform capable of advanced
analytics on semi-structured data. The platform includes APIs for building
apps and a phenomenal toolset for data science. Developers can use
our toolset for easy data analysis & visualization. Get a free account!
http://www2.precog.com/precogplatform/slashdotnewsletter

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users