3 Replies Latest reply on Apr 30, 2013 10:18 AM by philmodjunk

    Importing FMP date into SQL SERVER database

    tswalton@gmail.com

      Title

      Importing FMP date into SQL SERVER database

      Post

           Hi,

           I have a date (creation date) in a field in FMP12. I want to get it into SQL Server.

           In SQL Server I've created a table with a field of type datetime, and I've linked the table via ODBC to my fmp database.

           So all I do is run an import script step that imports data from a fmp table into my SQL linked table.

           This works fine with all fields except the date field, which comes through empty!

           Can you help me to get the date field to transfer nicely from fmp to sql server please?

           Many thanks in advance

           Tim

        • 1. Re: Importing FMP date into SQL SERVER database
          philmodjunk

               Different systems store dates with different internal data formats (FileMaker stores a date as the number of days since 12/31/0000) and thus, there might be an incompatibility that is keeping the data from importing correctly.

          • 2. Re: Importing FMP date into SQL SERVER database
            tswalton@gmail.com

                 I ended up doing something a little but round the houses, but it works.

                 In fmp I have a date field. I then create a calc field that turns that date into text, like this,
                 Day(dateCreated) & "." & Month(dateCreated) & "." & Year(dateCreated)

                 In SQL Server I have a text field that this calc field is imported into. So I now have a date in SQL Server, but as a varchar.

                 Finally I create a view that points at the varchar and converts it back to a date like this,


                      SELECT *,CONVERT(DateTime, dateCreated, 104) AS dateCreated FROM Orders

                  
                 (note, the 104 works with the fmp calc format that I've shown above).
                  
                 Maybe this will help somebody else trying to do a similar thing.
            • 3. Re: Importing FMP date into SQL SERVER database
              philmodjunk

                   If you research the way dates are stored in SQL Server, it may be possible to setup a less convoluted way to get this to work by using a calc field in FileMaker that computes a number that can correctly import into a date field in SQL Server.