3 Replies Latest reply: Apr 25, 2013 11:37 AM by Tridus RSS

    how to pass date time from PL to BAL , DAL to Database.

    947771
      hi,

      1) Please tel me how to pass date from .net using odp.net to oracle database for storage.
      so that it should not be affected if any one changes os(windows xp or 2003 /2008 server) date formate.

      database and application both are on different server and can be on same server also.
      so if any one keeps different date formate on both OS then it should not get affected.



      yours sincerely

      Edited by: 944768 on Apr 24, 2013 8:59 AM

      oracle 11g express

      Edited by: 944768 on Apr 24, 2013 8:59 AM
        • 1. Re: how to pass date time from PL to BAL , DAL to Database.
          Tridus
          Use the DateTime class in .net, and pass it to/from Oracle using the DateTime Oracle data type in your parameters. It'll handle the conversion on it's own.
          • 2. Re: how to pass date time from PL to BAL , DAL to Database.
            947771
            thanku,

            there are few places where date is passed as string '01 Jan 2013' and datatype is varchar.
            i do not want to changed this

            will stored procedure be able to handel this, in situations stated above.
            if not what measures i should take.

            yours sincerely

            Edited by: 944768 on Apr 25, 2013 7:56 AM

            Edited by: 944768 on Apr 25, 2013 7:57 AM

            Edited by: 944768 on Apr 25, 2013 8:01 AM

            Edited by: 944768 on Apr 25, 2013 8:02 AM
            • 3. Re: how to pass date time from PL to BAL , DAL to Database.
              Tridus
              If you're doing it with strings, you need to explicitly set the date format so that what you're passing in is consistent. For example, this would take a .net DateTime that was in any format, and turn it into a string:
              DateTime aDate = DateTime.Now;
              string aDateString = aDate.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture);
              That will work the same way no matter what you set the server date/time format to. You'll need to do the same thing when bringing dates back out of Oracle, and set it explicitly. The InvariantCulture stuff tells it to ignore different cultural settings, to ensure it works reliably.

              That said, by far the superior way of doing this is to use the Date and Time formats in Oracle, and to use DateTime objects in .net. Those are designed exactly for this scenario, and store the date in a neutral format so all you have to do is display it in the local format.