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!

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

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

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.

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.

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

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.

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.

*******..

Time(

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

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

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