Timestamp to Military Time

[LaRetta]

Searched first but no luck ...

I import a text field which is a concatenation of other fields. It is used as a primary key to incoming SQL data. The text key looks like this: 20070327_14:04:19_001133731A_1. This field must match us exactly because that's what ties us to the SQL source. I also import the fields which make up this key. They are (in order of their concatenation)

CrDate: A creation timestamp field which I import into a timestamp field and, unformatted at layout level, displays as: 3/27/2007 02:04:19 PM

PO_num: A purchase order number (number)

Line_num: A PO lineitem number (number)

I had to pull in some data without a primary key and I need to roll my own. But no matter what I try, the key always comes out as: 200703_2:04:19 PM_001133731A_1. I even changed my system format to remove the AM/PM and make it military time but nada. I realize I can horse the puppy but I'm hoping there is better way. There is no elegance in horsing.

What I want to know is ... what is the theory that FM uses to determine that a time should always inherit the AM/PM even if the starting field doesn't have it that way, even if the timestamp is set to military and even if the system time is set to military? No logic in sight ...

Edited January 30, 2008 by Guest
Shortened post

[LaRetta]

GetAsTime ( table::CrDate )

What we need is GetAsMilitaryTime ()

:laugh2:

[comment]

When you convert time to text, Filemaker is supposed to consult the current time format in effect and use that. Surely you will remember we found it doesn't always work as expected (I believe you would see a difference if you cloned your file under OS set to use 24-hrs format).

In order not to depend on the file/OS format in any way, you could use:

Year ( Timestamp ) & 

SerialIncrement ( "0000" ; 100 * Month ( Timestamp ) + Day ( Timestamp ) ) &  

SerialIncrement ( "_00" ; Hour ( Timestamp ) ) &

SerialIncrement ( ":00" ; Minute ( Timestamp ) ) &

SerialIncrement ( ":00" ; Seconds ( Timestamp ) )

[LaRetta]

You would think I'd have connected it to the date issue but I didn't. I guess it's because I don't see times as being 'international' or 'standard' (US); not that you'all aren't standard but you know what I mean. Oh! Oz time vs. US? How do others refer to the two different types?

Anyway, as usual, your code is perfect. I've enjoyed (a few times now) playing with your use of SerialIncrement and this example is no exception. It's sweeter to write:

SerialIncrement ( "0000" ; 100 * Month ( timestamp ) + Day ( timestamp ) )

... than to write

Right ( "00" & Month ( timestamp ) ; 2 ) & Right ( "00" & Day ( timestamp ) ; 2 )

In addition, yours is straight mathematics so would evaluate quicker (I think) AND it shows alternate techniques which can be used elsewhere. I confess that I thought, "Hey, why the 4 zeros? Why not 2?" So I tried: SerialIncrement ( "00" ; Month ( timestamp ) + Day ( timestamp) )

It gives 28 (adds them only) because they would then overlap. Thanks for the help on this issue and also for providing more mental fodder!

Edited January 31, 2008 by Guest

[comment]

I thought, "Hey, why the 4 zeros? Why not 2?" So I tried: SerialIncrement ( "00" ; Month ( timestamp ) + Day ( timestamp) )

This would have worked just as well:

...

SerialIncrement ( "00" ; Month ( timestamp ) ) &

SerialIncrement ( "00" ; Day ( timestamp ) ) &

...

But when I saw it like that, I thought, "Hey, why not combine them and use 4 zeros?"

Actually, you could even go to:

SerialIncrement ( "00000000" ; 10000 * Year ( Timestamp ) + 100 * Month ( Timestamp ) + Day ( Timestamp ) )

or, since the year is hardly expected to fall below 1000, just:

10000 * Year ( Timestamp ) + 100 * Month ( Timestamp ) + Day ( Timestamp )

[LaRetta]

SerialIncrement ( "00" ; Month ( timestamp ) ) &

SerialIncrement ( "00" ; Day ( timestamp ) ) &

I considered this one but it didn't feel very cool and not MUCH better than the ole Right() method. Four zeros rocks. As for your next two options listed ... ahhh what fun. Now THIS is what makes FileMaker exciting!!

If I can implant these theories then worlds will open up that I can't even imagine yet, ie, you can bet that (at some time, some place, when least expected) it'll emblazen through my brain and I'll use it in some other cool way. Of course I don't need to tell YOU that. :waytogo:

[Søren Dyhr]

The purpose here is an obvious example where SerialIncrement( can be deployed, but what had the mastermind behind it have in his/her thoughts when ushering into the tool set ... I wonder???

--sd

[LaRetta]

Michael, when I said:

SerialIncrement ( "00" ; Month ( timestamp ) ) &

SerialIncrement ( "00" ; Day ( timestamp ) ) &

I considered this one...

I meant AFTER you gave your first sample and not before. I didn't want it to sound like, "oh yeah, I thought of that already." Big diff.

Hi Soren, I agree totally! I am always MOST interested in the thinking to arrival rather than the outcome. I assumed here it's because SerialIncrement() is a natural (it appears) in replacing pre-padding. But 'the mastermind' behind this has used it in more ways than that; such as validation of strings. I doubt I've even seen the tip of the tip of the iceburg of its power yet. :wink2: