yyyymmdd string and YTD

[dmccallum]

How can I convert my delivery date in the format of a string (yyyymmdd) so that I can easily use the YTD function?

 

[dgillz]

I use Macola software and they store their dates as an 8 digit integer YYYYMMDD and they supply a numbertodate UFL to convert the number to a date. If you give me your email address I'll gladly email it to you.

dgilsdorf@altavista.com

What database are you working with by the way? I thought the YYYYMMDD integer was fairly unique. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[mbarron]

Create one of the following formulas:

This one will work for version 8 or later:
date(picture(totext({datefield},0),"xxxx/xx/xx&quot)

This one will work for all versions:
Date (tonumber(totext({datefield},0,"&quot[1 to 4]),
tonumber(totext({datefield},0,"&quot[5 to 6]),
tonumber(totext({datefield},0,"&quot[7 to 8]) ) Mike

 

[dmccallum]

Both formulas give me the error:

A statement is required here

 

[mbarron]

Did you replace the {datefield} with your the string field you are trying to change to a date? Mike

 

[FoxG]

Hi,

If the {datefield} is already a string the totext function is unnecessary !!

Just use...

Date (tonumber({datefield}[1 to 4]),
tonumber(datefield}[5 to 6]),
tonumber({datefield}7 to 8]) )

Geoff

 

[sarakaj]

date(picture(totext({reconcile.adjudication_DT},0),"xxxx/xx/xx&quot)

formula gives me an error
"To many arguments have been give to this function"
Please help!

 

[dmccallum]

Yes. It's {jobcost.del_date}

 

[dgillz]

if that field is a date, just use the NumbertoDate function I supplied:

NumbertoDate({jobcost.del_date})

Please let me know if you have any questions. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[sarakaj]

when using this formula

date(picture({reconcile.adjudication_DT},0),"xxxx/xx/xx&quot

I am getting "A string is required here and the cursor is at "0"

 

[dmccallum]

The field is a string such as:

20011217
yyyymmdd

 

[dgillz]

My last post sucked. Too early I guess. Using the NumberToDate function, if the field is an number in the YYYYMMDD format use the formula:

NumberToDate({jobcost.del_date})

If it is a text field in the "YYYYMMDD" format, then convert the text to a number first, then convert it to a date as follows:

NumberToDate(ToNumber({jobcost.del_date})) Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[sarakaj]

yes -exactly

 

[mbarron]

sarakaj:
if your {reconcile.adjudication_DT} is a string use:
date(picture({reconcile.adjudication_DT},"xxxx/xx/xx&quot)

if it is a number the following should work:
date(picture(totext({reconcile.adjudication_DT},0),"xxxx/xx/xx&quot)


FoxG:
You are correct. I'm a creature of habit and am accustomned to changing a number to the date using the formula I posted. Mike

 

[dmccallum]

date(picture(totext({reconcile.adjudication_DT},0),"xxxx/xx/xx&quot)

looks good but if it was in a mm/dd/yyyy format then you could use the YTD function. Is there a way to change the format?

 

[sarakaj]

mbaron:
This is driving me nuts: I am pulling tha data from MSSQL 7.0 and in database it is a "char" "20011002", when applying the formula in: Format editor - text interpretation

date(picture({reconcile.adjudication_DT},"xxxx/xx/xx&quot)
I get "the formula has to be a number"

but when using
NumberToDate(ToNumber({reconcile.adjudication_DT}))
i get "the formula result must be a number"

am i in the wrong format field editor or something?

 

[mbarron]

sarakaj:
You need to create a fomula field to change the date. The text interpetation area you mention has nothing to do with the change.

dmccallum: What are you getting when you but the formula in your report? It should be giving you mm/dd/yyyy format when it is placed in the report. Mike

 

[sarakaj]

mbarron:
Hey Mike:
Thank you very much it is working.
John

 

[dmccallum]

Mike: you're right, it does, thanks so much for your help.
Sarakaj: Could you post the formulas you used?