Number to Date Conversion

[Robin99]

Hi,

Using CR7 with Impact/Encore 5.1 ODBC

Today I'm writing a bookings report.
When I pull a "transaction date" field from our DB, it produces date values in the following format:

20011012
20030707
20040205

However, my desired output is mm/dd/yy.
The data type for this field in CR is "number", not "date", so I'm not presented with the "DATE" tab in the Format menu.

Do I need to use a formula to convert the above format to mm/dd/yy? If so, can anyone offer help?

Thanks in advance - RRR

 

[synapsevampire]

One way is:

cdate(val(left(totext(19700101,0,""),4)),val(mid(totext(19700101,0,""),5,2)),val(mid(totext(19700101,0,""),7,2)))

Replacing 19700101 with your field.

-k

 

[Robin99]

Hi Vampire,

Thanks. I tried the formula (replaced "19700101" with my fieldname), but I'm getting the following error msg:

"The remaining text does not appear to be part of the formula..." And my cursor is placed at the very beginning, right before "cdate".

Any ideas?

 

[synapsevampire]

Are you sure that it's a number, and not a string field?

Right click the field and select Browse Data.

I believe that CR 7 supported cdate, so try:

Cdate(val(left(19700101,4)),val(mid(19700101,5,2)),val(mid(19700101,7,2)))

If it's a string (not a number as your post stated), this should work.

-k

 

[Robin99]

Hi Vampire,

I right clicked the field, clicked "browse data" and the Type is in fact: "number"

I tried your subsequent formula just in case and got the same error message as before...


- RRR

 

[synapsevampire]

Where are you putting this formula?

When you receive an error, post what you tried, and describe where you tried it.

Use Insert->Field Object->right click Formula Fields and select New.

It should look like:

cdate(val(left(totext({table.numberfield},0,""),4)),val(mid(totext({table.numberfield},0,""),5,2)),val(mid(totext({table.numberfield},0,""),7,2)))

-k

 

[vidru]

SV's formula is sound. I don't know if the problem is related to your version.

I also don't know if the Mod function exists in CR7, but if it does, here's another formula you can try:

Date(int({Table.DateNum}/10000), int(({Table.DateNum}mod 10000)/100), {Table.DateNum}mod 100)

-dave

 

[Robin99]

Hi SV,

I'm in the Formula Editor.
My formula looks like this:

cdate(val(left(totext({table.numberfield},0,""),4)),val(mid(totext({table.numberfield},0,""),5,2)),val(mid(totext({table.numberfield},0,""),7,2)))

When I click the "check for errors" button, I get this error message:"The remaining text does not appear to be part of the formula..." And my cursor is placed at the very beginning of the formula.

I'm grateful for your assistance - please let me know if I've misunderstood your request for details...

 

[Robin99]

Hi Dave,

I tried your formula too, but also get an error message.
"The ) is missing..."
When I put the ) where my cursor lands, I get:
"The remaining text does not appear to be part of the formula..."

Sorry to be a pain in the posterior - I sure appreciate the help.
LMK if I can provide any more detail...

RRR

 

[synapsevampire]

You need to use YOUR table and field names, not my example names.

-k

 

[synapsevampire]

Does this execute?

cdate(val(left(totext(19700101,0,""),4)),val(mid(totext(19700101,0,""),5,2)),val(mid(totext(19700101,0,""),7,2)))

If so, then the only difference would be your number field.

-k

 

[Robin99]

Of course I used MY table names, I just didn't paste correctly in my reply. (shoot!)

Here's the real formula...

cdate(val(left(totext({SORADDITIONS.TrnDate},0,""),4)),
val(mid(totext({SORADDITIONS.TrnDate},0,""),5,2)),
val(mid(totext({SORADDITIONS.TrnDate},0,""),7,2)))

Sorry for the error...

 

[Robin99]

Hi,

No, I get the same error message on:
cdate(val(left(totext(19700101,0,""),4)),val(mid(totext(19700101,0,""),5,2)),val(mid(totext(19700101,0,""),7,2)))

Could it possibly be the "cdate"?
My cursor gets placed in front of it every time I get the "The remaining text does not appear to be part of the formula" error...

 

[Robin99]

EUREKA!
I FOUND IT!
I went to CR tech support site and found the following:

NumberVar YYYY;
NumberVar MM;
NumberVar DD;

YYYY := Truncate({SORCHANGES.TrnDate} / 10000);
If Truncate(({SORCHANGES.TrnDate} - (YYYY * 10000)) / 100) > 0 and
Truncate(({SORCHANGES.TrnDate} - (YYYY * 10000)) / 100) < 13 then
MM := Truncate(({SORCHANGES.TrnDate} - (YYYY * 10000)) / 100)
else MM := 01;
If Truncate(({SORCHANGES.TrnDate} - ( YYYY * 10000)) - (MM * 100)) > 0 and
Truncate(({SORCHANGES.TrnDate} - ( YYYY * 10000)) - (MM * 100)) < 32 then
DD := Truncate(({SORCHANGES.TrnDate} - ( YYYY * 10000)) - (MM * 100))
else DD := 01;

Date(YYYY,MM,DD);

And it WORKS!
Thanks to SV and Dave for their assistance!

- RRR

 

[bdreed35]

It appears that cdate() was not a function back in CR 7.

You can use sv's formula and just change the function name to "date" rather than "cdate".

date(val(left(totext(19700101,0,""),4)),val(mid(totext(19700101,0,""),5,2)),val(mid(totext(19700101,0,""),7,2)))

You don't need to change your existing formula, but it would be nice if you could create another formula just to prove that it was the cdate function. Please post the results if you don't mind.

~Brian

 

[Robin99]

Hi Brian,

I was in fact the cdate!
I went to CR Tech Support site again and downloaded a whitepaper on date/time formulas.
It says that the cdate function is not available until version 8.

I did as you requested and replaced "cdate" with "date" in SV's formula...
VOILA! It worked.

Thanks to all - For your time as well as your patience.

- RRR

 

[bdreed35]

Thanks for taking the time to try it out. It is nice to see a resolution in threads that are started.

~Brian