2 Replies Latest reply on Jan 3, 2012 5:00 AM by Charlie999

    Date formatting problem.

    Charlie999

      Title

      Date formatting problem.

      Post

      Hi

      I import a csv file regularly into my database that I download from a Joomla website of users.  It imports without problem.

      One field is a time and date field which imports as shown below as one example.

      2011-12-31 10:51:36

      My question is how do I get filemaker to recognise that as a time and date or just the date to be honest?  If I make the field a date field it shows the ? symbol when I ask Filemaker to change its format.  ie if I ask it to show 31 Dec 2011.  If I leave it as entered it shows it OK but does not seem to recognise it.  When I use find mode and type in //2011 nothing appears!

      I think I need a script to change the numbers about.

        • 1. Re: Date formatting problem.
          LaRetta_1

          Hi Charlie,

          It is important when dealing with date or time data that you use FileMaker's date and time functions instead of using text functions to parse out your results.  Date and time functions will properly adjust regardless of your file or OS regional settings. 

          You will need to import that textDate into a text field.  You can create a new timestamp field and set it at Auto-Enter tab to calculation of:

          Let ( [
          field = your text field ;  // field with data formatted as 2011-12-31 10:51:36
          d = Substitute ( LeftWords ( field ; 1 ) ; "-" ; ¶ ) ;
          t = RightWords ( field ; 1 ) ;
          mo = GetValue ( d ; 2 ) ;
          da = GetValue ( d ; 3 ) ;
          yr = GetValue ( d ; 1 )
          ] ;
          Timestamp ( Date ( mo ; da ; yr )  ; GetAsTime  ( t  )  )
          )

          I had a duplicate line in the calc.  I removed it.

          • 2. Re: Date formatting problem.
            Charlie999

            LaRetta

            Sorry for a late reply but Ive had a busy week at work.

            Anyway a big thanks for giving me that coding it worked a treat.  Ive looked at it a few times and dont fully understand how it works but never the less ive put it into my solution.

            Thanks again.