Convert Number to Time

[Nyangi72]

I need help converting a number to a Date & Time. The Date part works fine but I can never get the time to work correctly. My data is stored as a number
E.G
DATE:- 20,131,015.00
TIME:- 1,949.00.

[highlight #FCE94F]These two if converted to date time format should be 10/15/13 12;19:49 AM[/highlight]

Please find my code below

Function (numberVar v1, numberVar v2)

//Date Conversion
local stringVar MyDate := CStr(v1);
local stringVar YearVar := MyDate[1 to 2] + MyDate[4 to 5];
local stringVar MonthVar := MyDate[6] + MyDate[8];
local stringVar DayVar := MyDate[9] + MyDate[10];

//Time Conversion
local numbervar MyTimeNum:= Int(v2);
Local stringvar MyTimeStr := cstr(MyTimeNum);
if MyTimeNum<100000 then MyTimeStr := "0"+MyTimeStr;

Replace(MyTimeStr,",","",1);

//fill in array
local stringVar TimeHold := MyTimeStr;
local stringVar Allzeros := "00000000";
Replace(TimeHold,".","",1);
local numberVar z := Length(Timehold);

local stringVar HrsVar := mid(Timehold,1,2);
local stringVar MinVar := Mid(Timehold,z-7+1,1)+mid(Timehold,z-6+1,1);
local stringVar SecVar :=mid(Timehold,z-5+1,2);

DateTime(ToNumber(YearVar), ToNumber(MonthVar), ToNumber(DayVar),
ToNumber(HrsVar), ToNumber(MinVar), ToNumber(SecVar));

 

[briangriffin]

How is 1949 interpreted as 12:19:49 am?

If the time were 1:19:49 am would the database value be 11949? Would 1:19:49 pm be 131949?

Also, are the months and days always two digits?

 

[SkipVought]

hi,

DATE:- 20131015
TIME:- 1949

Our database has a similar date format, but they are NOT NUMBERS.

Your data, 20131015, is TEXT, that you must 1) parse into year, month and day as yyyymmdd using TEXT FUNCTIONS, and 2) convert to a date serial value, which IS A NUMBER.

I'd wager that your time TEXT value is really 001949 that you must 1) parse into hour, minute and second as hhmmss and 2) convert to a time serial value which IS A NUMBER.

I just happens that 001949 military format is 12:19:49 AM, while 121949 military format is 12:19:49 PM.

So you must left-pad the time TEXT with ZEROS to 6 characters, and then parse.



Skip,

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

 

[Charliy]

I love doing things with UDF's. Here is one I threw together that will take a four digit number representing military format time, and convert to an AM/PM format. It could easily be converted if your data is a string.

Function (numbervar tm24)
local stringvar xtim := totext(tm24,"0000");
local stringvar hh := left(xtim,2);
local stringvar mm := right(xtim,2);
local numbervar vhr := val(hh);
local stringvar ampm := (if vhr > 11 then " PM" else " AM");
if vhr > 12 then vhr := vhr-12;
local stringvar hcol := (if vhr = 0 then '12' else totext(vhr,0));
hcol&':'&mm&ampm