Combined Date and Time Field

[Fikse]

I am importing data from a database that has the date and time together in one field like this:

9/3/2003 1:31:49 PM

since there is no combined date and time field definition in filemaker pro, how can I search through this field when the date and time is combined? or separate the date and time into separate fields automatically?

thanks for any help!

[-Queue-]

Why not create a date calculation TextToDate( LeftWords( field, 1 ) ) or script a Set Field similarly?

[Lee Smith]

I would separate them out into their own fields.

There are some simple calculations available. here are a couple; for the Date:

new field

c_Date calculation, Date result:

Date(LeftWords(YourField, 1), MiddleWords(YourField, 2, 1), MiddleWords(YourField, 3, 1))

new field

c_Time calculation, Time Result:

Time(MiddleWords(YourField, 4, 1), MiddleWords(YourField, 5, 1), MiddleWords(YourField, 6, 1))

HTH

Lee

[-Queue-]

Ah yes, go with Lee's advice. I've been medicated and out of the loop for a few days. I didn't even think about the possible issues with specifying the delimiter and not using Date(). Oops.

[-Queue-]

Or you could (int) (assuming you're using PHP or the like to extract the data) the result to remove the decimal portion (time) from your data before it's imported.

[Lee Smith]

Wow, Queue - where can I get some of that stuff

I agree with Queue, that it is much easier to deal with the data prior to bring it into FileMaker, unfortunately, I have no idea what "PHP" stands for (I do see it used as a file tag to the downloads, i.e. as Download.php, for this Forum, but that's it).

Lee

[-Queue-]

Lee, the 'stuff' is basically just glorified Advil, taken 800 milligrams at a time. But after 12,000 milligrams of it, the brain does seem to be a bit rusty.

PHP is just a parser language that's very useful in extracting data from SQL databases and presenting them on the web. I use it for building reports and generating dynamic Excel spreadsheets for downloading. There are also packages for creating PDFs, Word docs, etc. It goes hand-in-hand with our FileMaker systems here.

[Ugo DI LUCA]

Hey Guys....

Bugs for sure, and something you developer should be aware of....if working cross the Atlantic.

As I already had to deal with this kind of structure, I was surprised to see Lee's calc here. The separators were actually different from what I had in mind.

The French settings, which I just verified doesn't consider ":" as a separator, which can produce awful results, in this case or in some others.

The result of Lee's calc for the Time extraction gave me 10204:00:00.

I know Lee always test his calc, so I sent him a PM, and we found out that there was a "bug", or a system setting to correct.

So to by-pass these settings, one should be using the following formula :

Time(MiddleWords(Substitute(YourText, ":"," "), 4,1) + Case(MiddleWords(Right(YourText,2) = "PM", 12),Middlewords(Substitute(YourText,":"," "),5,1),Middlewords(Substitute(YourText,":"," "),6,1))

Base on the following test, developers wishing to have "universal" settings should use a Substitute(text, ":"," ") if used in any WordCount functions.

Hope This may help. I thought I should have pointed this out.

[Ugo DI LUCA]

*******..

Time(

MiddleWords(Substitute(YourText, ":"," "), 4,1) + Case(MiddleWords(

Right(YourText,2)) = "PM", 12),Middlewords(Substitute(YourText,":"," "),5,1),Middlewords(Substitute(YourText,":"," "),6,1))

[Fikse]

Lee, thank you for posting the calculation.... works great...