Date Manipulation Once Again!

[MrFuzzwah]

Well I have to ask this but I need to convert a number field to a date for comparison. Here is an example of a number field which contains the date: 61512. I need to convert this thing to a date but after 2 days I cannot get it done. Your assistance is appreciated.

 

[SkipVought]

hi,

You do not REALLY have a NUMBER, do you?

I mean 61512 as a NUMBER is 6*10⁴+1*10³+5*10²+1*10¹+2*10⁰.

But your "number" is REALLY a STRING OF NUMERIC CHARACTERS that represent Month 6, Day 12 and Year 2000+12, or am I all wet, out to lunch and whisslin' in the dark?

Actually, Date/Time values are NUMBERS, but NOT the "number" that you have represented. So you will need to CONVERT your STRING to a Date/Time (numeric) value, using a Date/Time function, like the DateSerial() function, by parsing your STRING into the YEAR, MONTH and DAY components in the appropriate function argument.

Skip,

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

 

[MrFuzzwah]

Thanks for the response - much appreciated.

Using this formula:
stringvar x := split(totext({PAYROLLDB_EVENT.TRANDATE},"0000000"),".")[1];
date(val(right(x,2)

A 'date' field (I get your point about the data type) such as this:
110907

yields this result: 01/06/1900

I'm stuck on getting the year. I would like for the output to be DD/MM/YYYY.

 

[MrFuzzwah]

I need to say - I really need to sort by date which is why my formula addresses the Year - for now. I will have to do records for 4 years after pulling current year first.

 

[SkipVought]

WHAT is it that you need to do with the DATE {PAYROLLDB_EVENT.TRANDATE} that you have?

I really need to sort by date

Then SORT by {PAYROLLDB_EVENT.TRANDATE}, assuming that it is a real date.

No need to do hand stands an flips.

Skip,

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

 

[MrFuzzwah]

It is not a real date - it is treated as a number or numeric value. That's the need for conversion Skip.

 

[MrFuzzwah]

Properties - in SQL - is decimal for this trandate field.

 

[SkipVought]

OK, before we proceed any further, so how is June 1 2012 represented in your "number"?

Skip,

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

 

[MrFuzzwah]

60112

 

[SkipVought]

Let me comment, that whoever designed this "date" field in THIS way, mdddyy, was 1) ignorant and 2) ought to be hanged by the thumb until numb!

All you need do is PARSE (you cannot use the Split() function as there is no CHARACTER upon which to parse), parse the Year, Month & Day into the arguments of the DateSerial() function, using the MID() starting with your original attempt...
[tt]
stringvar x := totext({PAYROLLDB_EVENT.TRANDATE},"000000");
dateserial(2000+val(mid(x,5,2),val(mid(x,1,2),val(mid(x,3,2))
[/tt]
Notice that the "number" is converted to a 6-character text string, not 7 characters as in your example.

This also assumes that you ONLY have twenty-FIRST century dates, hence adding 2000 to the year value.

Skip,

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

 

[MrFuzzwah]

Thanks for your help - that appears to have worked after a few ')'s were inserted. There are dates in this system formatted in a variety of ways.

Down here in the basement we are not really happy with this system but it keeps you busy.


Thanks again

 

[SkipVought]

I'druther be busy doing things other than crutching other people's mistakes.

But glad its working for you now!

Skip,

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