adding leading zeroes to numbers 1

[merlynsdad]

I'm working with an Oracle db that handles times strangely. First of all, it uses number types, so that 10:20:25 AM is equal to 102025. I can convert anything after 10AM ok because the number contains 6 digits and I have a SQL clause to do that:

& "FormatDateTime(TimeSerial(left(" & QryLoc & "_DTA_AAC_EVENT.time_stamp,2), mid(" & QryLoc & "_DTA_AAC_EVENT.time_stamp,3,2),right(" & QryLoc & "_DTA_AAC_EVENT.time_stamp,2)),3)AS [time_stamp]," _

Prior to 10AM, however, the db drops leading zeros on the numbers. 7:30:45AM is thus 73045, 12:30:45AM is 3045, 12:00:45 is 45, all the way to 12:00:01 which is 1.

How do I add leading zeroes back on these numbers?

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

hi,

If your Date/Time field is DT, then the time part conversion to text is...

Format(DT, "hhmmss")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

How is your field [tt]_DTA_AAC_EVENT.time_stamp[/tt] defined in your Oracle db?

Is it Oracle's DATE field? Or simple NUMBER?

Maybe it is not "Oracle db that handles times strangely"....?

Have fun.

---- Andy

 

[merlynsdad]

_DTA_AAC_EVENT.time_stamp is a number in Oracle, from one to six digits. When I bring it into Access I'm converting it to time on the fly with that FormatDateTime(TimeSerial formula so it arrives in Access tblTemp_Event.[time_stamp] as time. This works finr for six digit Oracle numbers. But I'm trying to avoid converting twice, so I need a way to handle the Oracle numbers that delete the leading zeroes, which would be times prior to 10AM.

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

OK, I'm trying something that seems like it would work (or at least be close), but it involves the SWITCH function which I've never used before. I'm trying this code:

strSQL = "INSERT INTO tblTemp_Event (time_stamp_orig) " _
& "SELECT " & QryLoc & "_DTA_AAC_EVENT.TIME_STAMP " _
& "SWITCH (len(" & QryLoc & "_DTA_AAC_EVENT.time_stamp ) = 5,time_stamp_orig = 0 & RIGHT(" & QryLoc "_DTA_AAC_EVENT.time_stamp,5) " _
& "FROM " & QryLoc & "_DTA_AAC_EVENT;"
Debug.Print strSQL
db.Execute strSQL

Time_stamp_orig is a number in Access. I'm getting a missing operator run-time error. Also, this is just addressint the 5 digit Oracle numbers; I've still got to address the 4,3,2 and 1 digit numbers.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

A DATE is a DATE. TIME is a TIME.

Leading zeros mean ABSOLUTELY NOTHING to NUMERIC VALUES like DATE/TIME.

If you have a Date/Time value in Oracle, it's inherent value is NUMERIC in Oracle AND in Access. Why convert to STRING?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

It's NOT a date/time type in Oracle; it's a NUMBER type. They go from 0 (midnight) to 235959. I need to work with them as time in Access, not numbers, hence I'm converting them from a number data type to time/date type in Access. While it is possible to work with them in Access as numbers that are formatted as time, and look like time, we need to work with them as actual time.

And while the lack of leading zeros may mean nothing in Oracle as numbers, they're causing problems with my conversion of those numbers to time in Access.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

235959 my be comprised of digits, but it is nothing that you can do arithmetic with, so it must be treated as a STRING of the form hhmmss. So if your field is TS, then...

msgbox TimeSerial(MID(format(TS,"000000"),1,2),MID(format(TS,"000000"),3,2),MID(format(TS,"000000"),5,2))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

Actually, that's exactly what the original author of this db was doing - arithmetic with numbers like 235959! He converted everything to seconds, did his math, and converted it back. Messy, but it actually worked. I'm trying to change it. Sorry if I sounded frustrated, but you know the story of the guy who's job was to drain the swamp, but he ended up hip deep in alligators? That's me. Your example works perfectly, so thanks a bunch!

If the square peg won't fit in the round hole, sand off the corners.

 

[strongm]

The shorter

MsgBox CDate(Format(TS, "0#:##:##"))

may also suffice

 

[SkipVought]

arithmetic with numbers like 235959

Actually he could NOT do arithmetic with this 'number'

Rather, he had to PARSE the 'number' (things you do with STRINGS) and CONVERT the parsed segments, in order to arrive at a NUMBER that he COULD do arithmetic on.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}