0 Replies Latest reply: Dec 9, 2010 5:31 AM by 822501 RSS

    VBA Date Time Format problem

    822501
      Hello,

      I have a date time problem with a sql-request from Excel 2002 on XP, O-Client 9x and 10x to my database.

      Here is the end of my sql statement.

      datum(1) = "to_date ('" & UserForm1.TextBox26.Text & " 00:00:00', 'dd.mm.yyyy hh24:mi:ss')"
      datum(2) = "to_date ('" & UserForm1.TextBox27.Text & " 23:59:59', 'dd.mm.yyyy hh24:mi:ss')"
      sql_string(6) = "and ML.datum_zeit between " & datum(1) & " and " & datum(2)

      The text is correctly, p.e. 25.11.2010 for the first date and 07.12.2010 for the last date.

      Here is an example of the result:
      11/25/2010 12:25:26 AM
      12.08.2010 10:56
      I get only dates with the 12th day of any month from january to juli of this year, the dates of 25. to 30. november and no dates from december.
      The given date format is different between the november and 12th day dates, like in the example.

      In Windows 7 and O-Client 11x it works correctly, but I have many PC with XP and the older Client. How can I change my code?
      In sql-developer on the same XP pc it works also correctly, but it may be, that I have in this case an odbc-connection.

      I tried this:
      ' objDatabase.ExecuteSQL ("ALTER SESSION SET NLS_LANGUAGE = 'GERMAN'")
      ' objDatabase.ExecuteSQL ("ALTER SESSION SET NLS_TERRITORY = 'GERMANY'")
      ' objDatabase.ExecuteSQL ("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'")
      ' objDatabase.ExecuteSQL ("ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'")
      but it doesn't help... openSession has no Execute command, and objDatabase has only an ExecuteSQL method

      Here is the complete code to open the database connection:

      'Datenbankvariablen
      Public objSession As OraSession
      Public objDatabase As OraDatabase
      Public objDynaset As OraDynaset
      Public objFields As OraFields

      Sub db40open()
      ' Use OO4O - OracleInProcServer
      Set objSession = CreateObject("OracleInProcServer.XOraSession")
      Set objDatabase = objSession.OpenDatabase("myDBname", "/", 0)
      objDatabase.ExecuteSQL ("ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'")
      End Sub


      Thanks and regards,

      Edited by: user13444635 on 09.12.2010 03:25