Julian Date and Military Time

[DBFIN]

How can I convert today's date to Julian Date and Military Time format. I want to use a standard function or expression, since I won't be using any hard-coded language.

Time Stamp: Field Length 13, Format:YYYYDDDHHMMSS

Example: April 11, 2000, 6:02:23pm = 2000102180223

 

[RuralGuy]

Using the Format() function, you can display a DateTime field in either of the formats you have shown.

 

[DBFIN]

I tried the following expression for July 9, 5:09:05pm and got a result that is close. Instead of Wednesday, the result should be the 191st day of the year 2008.

Expression: Julian Date: Format(Now(),"yyyydddhhmmss")
Incorrect Result: 2008Wed170905
Correct Result: 2008191170905

How can I convert Wed to 191 using a simple query expression, since I won't be using hard-coded language ?

 

[RuralGuy]

Try Format(YourDate, "yyyyyHhNnSs")

 

[raskew]

How can I convert Wed to 191

? datepart("y", date())
191

Not sure what military you're with, never saw it done like that in mine.
Anyway---

? year(DATE()) & datepart("y", date()) & format(timevalue(now()), "hhnnss")
2008191171346

HTH - Bob

 

[RuralGuy]

Didn't like mine Bob?

 

[raskew]

Allan -

27+ happy years, but who's counting

We used 'julian dates' (they aren't really) for things like prefixes on a contract number, but never saw it used with time. Problem is, if someone
throws 218 at you, you'll need a special calendar to translate what they mean.

Oh well.

Best Wishes - Bob

 

[RuralGuy]

Hi Bob,

27+ happy years, but who's counting

I think I missed something.

Best Wishes from the Rocky Mountain High.

 

[raskew]

I think I missed something.

No, it was me. Wasn't sure what you were referring to. Thought it was the "Not sure what military you're with ...." statement. Sorry about that.

Guess I glossed over your response when I didn't see anything that seemed like it was going to return day of year. Now that I've tried it I'm both impressed and mystified.

? Format(now(), "yyyyyHhNnSs")
2008191224030

? format(date(), "yyyyy")
2008191

Is that just your little secret or have I seriously missed something. Never saw that before, nor can I find a reference to it!

Please enlighten me.

Best wishes - Bob

 

[DBFIN]

The following expression is very close, however if the day of the year is less than three digits I lose the leading zeroes that ensure a total field length of 13.

Format(now(), "yyyyyHhNnSs")

For example January 1, 2000 at 06:23:05 AM

Incorrect Result: 20001062305
Correct Result: 2000001062305 (with two leading zeroes before day 1)

How can I ensure a fixed 13 field length ?

 

[RuralGuy]

...Is that just your little secret or have I seriously missed something. Never saw that before, nor can I find a reference to it!

Please enlighten me.

Best wishes - Bob

I don't know if I would call it a secret. I just thought it might work so I tried it.

 

[RuralGuy]

Try this one:
Format(ADate, "yyyy") & Format(Format(ADate, "y"), "000") & Format(ADate, "HhNnSs")

 

[raskew]

That works like a champ! What had me baffled in your previous post was the
use of "yyyyy" to return both year and day-of-year. Never dawned on me to combine 'yyyy' and 'y' like that.

 

[RuralGuy]

It just depends on how the parser works. I tried it and it worked so...taa, daa.

 

[DBFIN]

I greatly appreciate your outstanding expertise. Thanks RuralGuy and raskew. You've solved this issue. I hope it's not asking too much to review my other inquiry in the "general" thread regarding Exporting Querys without Delimiters.

Once again, thank you so much !