none
Getting date from oracle RRS feed

  • Question

  • Hello,

    I have a big problem, my c# application using Oracle database.

    Why get back different date in different time zones from Oracle DB?

    This is the valid date in the oracle database: 2013-01-01 12:00:00

    If I query this date in the "B" time zone, I got valid date: 2013-01-01 12:00:00

    But If I query this date in the "C" time zone, I got non-valid date: 2013-01-01 19:00:00, I don't understand what is wrong...

    Oracle server time zone: (UTC-05:00) Eastern Time (US & Canada)
    Application time zone (1): (UTC-06:00) Central Time (US & Canada)
    Application time zone (2): (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague

    I save the hungarian time in hungary (2013-01-01 12:00:00) and I get that time in the USA, I get this: 2013-01-01 19:00:00

    Thank you for your help.

    • Moved by CoolDadTx Wednesday, December 4, 2013 2:54 PM ADO.NET related
    Wednesday, December 4, 2013 12:51 PM

Answers

  • The world standand is to store time in UTC (Greenwich London time).  Any application reading the time should display the actual time in local time.  So you normally read the time from another computer using  UTC time option  and then simpile display the time with no UTC option.  it appears yo uare display the output using the UTF option.  Just eliminate the UTF from the display and it should work proper.

    jdweng

    • Marked as answer by Robert Varga 2 Wednesday, December 4, 2013 4:30 PM
    Wednesday, December 4, 2013 1:19 PM

All replies

  • The world standand is to store time in UTC (Greenwich London time).  Any application reading the time should display the actual time in local time.  So you normally read the time from another computer using  UTC time option  and then simpile display the time with no UTC option.  it appears yo uare display the output using the UTF option.  Just eliminate the UTF from the display and it should work proper.

    jdweng

    • Marked as answer by Robert Varga 2 Wednesday, December 4, 2013 4:30 PM
    Wednesday, December 4, 2013 1:19 PM
  • Thanks your answer.

    Your answer could help to me to find the optimal solution.

    I modified the query, and the .NET can not recognize the DateTime type.

    ex.:

    SELECT START_DATE FROM TABLE;
    SELECT TO_CHAR(START_DATE,'ORACLE_TIME_FORMAT') FROM TABLE;

    Thank you again!



    Wednesday, December 4, 2013 4:35 PM
  • Do not make the date text.  That is causing the problem.  Get the date as date format.

    SELECT START_DATE FROM TABLE;


    jdweng

    Wednesday, December 4, 2013 4:43 PM
  • Oracle has the following Datetime enumeratios that Oracle.Client in the net library wil automatically recognize.  do yo uknow which type is in the Database?  Hopefully it is just DateTime.  If this is the case the time will get transfered as UTF automatically.  Then in you VS application just output the time like any other datetime and it should display normally.  No translation are required it is handled automatically.

            DateTime = 6,
            //
            // Summary:
            //     An Oracle INTERVAL DAY TO SECOND data type (Oracle 9i or later) that contains
            //     an interval of time in days, hours, minutes, and seconds, and has a fixed
            //     size of 11 bytes. Use the .NET Framework System.TimeSpan or OracleClient
            //     System.Data.OracleClient.OracleTimeSpan data type in System.Data.OracleClient.OracleParameter.Value.
            IntervalDayToSecond = 7,
            //
            // Summary:
            //     An Oracle INTERVAL YEAR TO MONTH data type (Oracle 9i or later) that contains
            //     an interval of time in years and months, and has a fixed size of 5 bytes.
            //     Use the .NET Framework System.Int32 or OracleClient System.Data.OracleClient.OracleMonthSpan
            //     data type in System.Data.OracleClient.OracleParameter.Value.
            IntervalYearToMonth = 8,
            //
            // Summary:
            //     An Oracle LONGRAW data type that contains variable-length binary data with
            //     a maximum size of 2 gigabytes. Use the .NET Framework Byte[] or OracleClient
            //     System.Data.OracleClient.OracleBinary data type in System.Data.OracleClient.OracleParameter.Value.
            Timestamp = 18,
            //
            // Summary:
            //     An Oracle TIMESTAMP WITH LOCAL TIMEZONE (Oracle 9i or later) that contains
            //     date, time, and a reference to the original time zone, and ranges in size
            //     from 7 to 11 bytes. Use the .NET Framework System.DateTime or OracleClient
            //     System.Data.OracleClient.OracleDateTime data type in System.Data.OracleClient.OracleParameter.Value.
            TimestampLocal = 19,
            //
            // Summary:
            //     An Oracle TIMESTAMP WITH TIMEZONE (Oracle 9i or later) that contains date,
            //     time, and a specified time zone, and has a fixed size of 13 bytes. Use the
            //     .NET Framework System.DateTime or OracleClient System.Data.OracleClient.OracleDateTime
            //     data type in System.Data.OracleClient.OracleParameter.Value.
            TimestampWithTZ = 20,
            //
            // Summary:
            //     An Oracle VARCHAR2 data type that contains a variable-length character string
            //     with a maximum size of 4,000 bytes. Use the .NET Framework System.String
            //     or OracleCl


    jdweng

    Wednesday, December 4, 2013 4:57 PM
  • You mean is it just a setting in the Oracle provider?

    Sorry, I don't exactly understand your recommendation.

    Is the solution similar even big amount of data?

    Thanks for your help.
    Wednesday, December 4, 2013 6:25 PM
  • From the data your are getting below nothing is wrong.  Windows will save time in UTC but display the time in local time.  So when hungary save time at Midnight NYC is a 19:00 which is exactly the way it should work.

    I save the hungarian time in hungary (2013-01-01 12:00:00) and I get that time in the USA, I get this: 2013-01-01 19:00:00

    hungary is +1 hour from UTC and USA East Coast is -5.  there should be a 6 hour difference using Standard time and 5 hour differce using Daylight Saving Time.  It looks like the time is during Day Light Saving Time EDT.

    I think you may a typo and the USA Date which should be December 31.


    jdweng

    Wednesday, December 4, 2013 6:51 PM