Retrieving a DateTime value with OracleClient

[Bernhard Hiller]

Hello, I am trying to read a DateTime value from an Oracle database (Oracle Version 11.2.0.1.0 running on a German Windows XP). The column is defined as "DATE", and the value is correctly shown as "2010-05-04 16:17:10.0" via the web browser when viewing all the data of the respective table. But when I retrieve the data with the OracleClient (via OracleCommand and OracleDataReader) in a C# application (.NET 2), I receive a string (!) value of

"04.05.10 "

, i.e. reader["LASTLOGIN"] returns a string with that value which I then convert to a DateTime using the Convert.ToDateTime() function. By the way, a

            int lastLoginCol = reader.GetOrdinal("LASTLOGIN");
            LastLogin = reader.GetDateTime(lastLoginCol);

yields a "Specific cast is not valid" error message. Please note the 6 blanks after the date part. Where has the time part gone to? How can I retrieve the full date time value? Is the answer: "Not at all, it's Oracle"? And what about a situation where the server is running with a different locale than the client? Is it really necessary to change to CHAR 14 (i.e. YYYYMMDDhhmmss - that does work with some tricks)? Thanks a lot for your ideas! Bernhard

[Chris Meech]

The Oracle DATE type for the column does not have a corresponding equivalent within most languages you will use. As a result it must be cast to some other intrinsic type. Usually what will happen is that Oracle will convert it to a string using a session specific date format specification. Look up on NLS_DATE_FORMAT and if you don't know what that format is, the following might help you

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

:)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[David Skelly]

In .NET all you have to do is open an OracleDataReader from an OracleCommand and then call GetDateTime. It works for me. .NET should handle all the type conversion issues: the OracleDataReader "knows" how to convert SQL types to .NET types so that I don't have to. It's called encapsulation. Or maybe abstraction, I can never remember the difference between those two.

[Bernhard Hiller]

Thanks a lot for your input. The problem has dissolved somehow - I use to shutdown the server and the client in the evening and start them again next morning, and now the problem cannot be reproduced. Maybe there was some inconsistent state in the Oracle database, during development things can go wrong and Oracle likes to punish you horribly for the smallest misdeeds....