Home » SQL & PL/SQL » SQL & PL/SQL » Converting Dates
icon5.gif  Converting Dates [message #219879] Fri, 16 February 2007 08:59 Go to next message
abis123
Messages: 31
Registered: February 2007
Member
My problem is that I am trying to convert a datetime I have in my ASP.NET C# code. At the moment I can get my code to produce a date and time like this:

01-Jan-2006 17:45:00

But when I try and pass this to my package it gives me this error message:

An error occured while uploading allegationSystem.Exception: ORA-01830: date format picture ends before converting entire input string ORA-06512: at line 1

Does anyone know why this is happening? And what I can do about it to stop it.
Re: Converting Dates [message #219880 is a reply to message #219879] Fri, 16 February 2007 09:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It means that most likely either
1) You are not specifying an explicit format mask when you convert that string to a date,
SQL> select to_date('01-jan-2000 12:23:34','dd-mon-yyyy') from dual;
select to_date('01-jan-2000 12:23:34','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
or
2) You are specifying a date format mask, but it is shorted than the date you are trying to convert:
SQL> alter session set nls_date_format='dd-mon-yyyy';

Session altered.

SQL> select to_date('01-jan-2000 12:23:34') from dual;
select to_date('01-jan-2000 12:23:34') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
Re: Converting Dates [message #219900 is a reply to message #219880] Fri, 16 February 2007 10:30 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
First, always remember that "01-Jan-2006 17:45:00" is just a string of characters. It may LOOK like a date to you and me, but to Oracle, it's only characters.

Second, it is always best to convert the character string to an Oracle DATE type explicitly, such as "to_date('01-Jan-2006 17:45:00','DD-Mon-YYYY HH24:MI:SS'). That way you'll always know that you've converted the string that looks like a date to you and me, to the actual DATE format that Oracle understands.

Third, when comparing dates, make sure that you compare a DATE type to a DATE type. Make use of the to_date function to do this.

Finally, use the TO_CHAR function to convert the DATE type variable back to a string that you can read and understand as a date. Don't depend on the default date settings.

Hope this helps,
Ron
Previous Topic: How to get missing values
Next Topic: How to Display as well as Insert in a single sql statement
Goto Forum:
  


Current Time: Sun Dec 04 18:38:30 CST 2016

Total time taken to generate the page: 0.03972 seconds