1 Reply Latest reply on Feb 28, 2013 2:16 PM by Akeso

    Retrieve Time from Oracle 10g Date Field

    Akeso

      Title

      Retrieve Time from Oracle 10g Date Field

      Post

           I am trying to setup an ODBC connection to a local Oracle 10.2.0.3.0 server.
           However, I am having a recuring issue with Date field imports. The company which runs the Oracle server stores time data within Date fields. Whenever I connect to their server, FM only pulls the date information.

           I have tried doing direct imports from the database into fields that are text and timestamp however the issue persits.

           In the attached SS all of the FM fields are text. All except the Time_Stamp field are pulling from Date fields and Date_Time should have an attached time.

           I think it's an issue similar to changing the Database NLS settings in Oracle SQL developer to show Dates as DD-MM-RR HH24:MI:SS, however I cannot find a way to make this change in filemaker.

           Suggestions? 

      FM-Import_SS.png

        • 1. Re: Retrieve Time from Oracle 10g Date Field
          Akeso

               In case anyone is ever interested:

               Finally managed to work it out. Solved the problem using a To_Char command in the SQL engine.
               Gave the proper output in FM when executed independant of the SQL query builder.

               Query ended up being-

                

               select to_char(Date_Time,'DD-MM-YY HH:MI:SS'), to_char(Date_Only,'DD-MM-YY HH:MI:SS'), to_char(Time_Only,'DD-MM-YY HH:MI:SS'),to_char(Date_Null,'DD-MM-YY HH:MI:SS'),to_char(TS,'DD-MM-YY HH:MI:SS')

                

               from date_test