Converting a string of numbers to a date

[Baylah]

Hi All,

I'm back with another of those questions I should know how to do but don't!

I have a set of data that I have imported from our Corporate Main frame where the dates (for G-d knows what reason) are stored as such:

20031029 (equals October 29, 2003)

Can someone help me with a formula that would convert these cryptic dates into something like in the parentheses or a simple date like Oct. 03. I actually need both results.

Thanks,

Steve

[Genx]

Let( x = "20031029" ;

Date( Middle(x ; 5 ; 2 ) ; Middle( x ; 7 ; 2 ) ;Middle( x ; 1 ; 4 ) )

)

[Baylah]

Let( x = "20031029" ;

Date( Middle(x ; 5 ; 2 ) ; Middle( x ; 7 ; 2 ) ;Middle( x ; 1 ; 4 ) )

)

Alex,

Does the Let statement apply to the field name that contains the dates?

Steve

[Genx]

Ah yeh, you don't really need the let, it's just clearer with it... so either replace "20031029" with YourTable::YourField

.. or

Date( Middle(YourTable::YourField; 5 ; 2 ) ; Middle( YourTable::YourField ; 7 ; 2 ) ;Middle( YourTable::YourField ; 1 ; 4 ) )

[Baylah]

Hey Alex,

This isn't working for me so far....But instead of begging for you for a solution, can you explain to me what is going on so I can see if I can figure out how to make it work?

I'll resort to groveling if I can't get it right!

Steve

[Genx]

Lol, love it.

Okay.

Date( Month ; Day ; Year )

Whenever you want to convert a set of numbers to a date type in FileMaker you use that (or get as date, but this is safer due to conflicting date formats) .

So for example, Date( 12 ; 10 ; 2006) would produce a date of December, 10th 2006.

Now, given that we have the above string of YYYYMMDD - we have to each portion of the info we need for the date function.

1) Month - starts at the fifth character, proceeds two characters: Middle( "YYYYMMDD" ; 5 ; 2 ) - will return MM for us.

2) Day - starts at the seventh character and proceeds two characters ; Middle ( "YYYYMMDD" ; 7 ; 2)

3) Year - starts at the first character and proceeeds four characters: Middle( "YYYYMMDD" ; 1 ; 4 )

Stick that all together and you get:

Let(

[

myField = Yourfieldhere;

myMonth = Middle( myField ; 5 ; 2 );

myDay = Middle( myField ; 7 ; 2 );

myYear = Middle( myField ; 1 ; 4 )

];

Date( myMonth ; myDay ; myYear )

)

If you're still confused, let me know.

Edited June 15, 2007 by Guest

[Vaughan]

"..where the dates (for G-d knows what reason) are stored as such: 20031029 (equals October 29, 2003)"

Because it's a really sensible way of storing and displaying dates -- I believe it's an ISO standard.

Among other things, it sorts correctly. It's also really easy to parse out.

Be glad your mainframe folks got it right!

[Genx]

it sorts correctly

You know, that never really occurred to me -- quite cool Now I get why all the enterprise db's use it.

[Baylah]

Because it's a really sensible way of storing and displaying dates -- I believe it's an ISO standard.

I was just trying to be funny! I knew there was a good reason! Sounds like you would love our green screen AS-400 system!

Steve

PS Alex, Thanks for the explanation. I have not had a chance to re-enter the stuff yet but I will in the morning as our IT staff won't give me remote access to my desktop. Woe is me

Steve

Edited June 15, 2007 by Guest

[Baylah]

Bing....Done...I get it.

Thanks,

Steve

[Baylah]

Alex,

I got the original question naileed...And I am only asking this because I am in a hurry and I can;t seem to get this to work right. Ihope you are out there.

How do I convert 20031029 to Oct03?

Steve (Working on it while I wait) Freeman

[mr_vodka]

If you just want to display it as Dec03, you can have it be formatted as that. Right click on the date field and there will be a Date Format option. Under there, you will need to selet the Custom option and change it so that it will display in the manner that you wish.

[comment]

Or, if you must have it as text, you could use something like:

Left ( MonthName ( Date ( Middle ( Input ; 5 ; 2 ) ; 1 ; 1 ) ) ; 3 ) & Middle ( Input ; 3 ; 2 )

---

If you have already computed the date as date, you could use that as the basis:

Left ( MonthName ( cDate ) ; 3 ) & Right ( Year ( cDate ) ; 2 )

Edited June 15, 2007 by Guest

[Lee Smith]

You are not following what is being said.

First of all, the number [color:red] 20031029 is [color:blue]not a date, it is either text, or numbers depending on the field. But it is not a date. The first thing you need to do is to convert the number [color:red] 20031029 to an actual date. For this, Genx has provided a Let calculation that will do this.

You can then display have a date field based on the calculation [color:red]731517 in this case.

That date field can be changed at the layout out to show the results as Oct29 if that is what you want.

HTH

Lee

[mr_vodka]

I am following what is being said. However, Baylah has already taken the orig format and as per Genx's directions changed it into a proper date format. Therefore, since it is already done, he can just use the Date formatting options to show him just what he wants.

[Lee Smith]

It doesn't sound like he understood that part. Since I deleted a duplicate post before posting here, and it contained the same request about converting the number to the Month day format.

Lee

[Baylah]

I was able to convert the number to a date as Alex suggested. It worked great.

Also, I did try format it as a Year and Month in the date format drop down, and it worked as I had hoped when viewing individual records.

However, when I included that field in a report and did a sub-summary based on that field even though I formatted it as a month and year all of the grouping was still based on individual days. So a grouping might have as many as 31 lines as opposed to just one, which is what I wanted.

I felt the only way to correct that was to strip the date out of the equation so I could get the report to display properly. Was my logic incorrect in that manner?

Steve

[Genx]

Format won't do anything, you have to actually create a calculation field as Michael (comment) suggested above. Format only changes the way it looks, a calculation field will manipulate the data and therefore change the way your report will work.

If you have already computed the date as date, you could use that as the basis:

Left ( MonthName ( convertedDate ) ; 3 ) & Right ( Year ( convertedDate ) ; 2 )

I.e. make ANOTHER field and use the calculation above.

[Baylah]

I am going to play with this when i get home because I have run out of time at work.

I have pulled 50,000 records out of the 4.5M record Dabse so when I test it doesn;t take forever.

Anyway, I ran this one time with the Calc. as above and it returned the month for me but did not give the year. I will try playing some more. Hopefully I can get this on my own.

I ended up giving the report to my boss with the date as 200311. He said the data was more inportnat than the format and he didn;t care, but I was still bummed that I could not present it the way I wanted to which would have been Dec. 03. (as well as the other 55ish months) in the report.

Thanks for everyones help. I have learned so much in the past couple of weeks.

Steve

[Genx]

Okay, just forget all the other fields... You can sort by your old 20031029 field seeing as it works, for your calc field just run this:

Let(

[

myField = Yourfieldhere;

myMonth = Middle( myField ; 5 ; 2 );

myDay = Middle( myField ; 7 ; 2 );

myYear = Middle( myField ; 1 ; 4 );

myDate = Date( myMonth ; myDay ; myYear )

];

Left( MonthName(myDate) ; 3 ) & Right( Year(myDate) ; 2 )

)

EDIT: Then run your report with your subsummary based on this field, however sort using your YYYYMMDD field, ... on second thought's that won't work, you'll need to make a year, month calculation field (result number) instead:

Left(YYYYMMDD Field ; 6) -> Sort by this, and your subsummary can be based on this as well.

HTH, goodluck!

Edited June 16, 2007 by Guest

[Baylah]

Well,

Attached is a very abbreviated set of my data. If anyone wants to take a stab at it I'd appreciate it. I have tried what has been suggested...Maybe I am just missing some small piece of the syntax, but I can't get it to work right.

This version only has the three fields I have been working with. It does not have the report layout or script.

Thanks,

Steve

Dates.zip

[Genx]

This calculation should be result text not date:

deIND_MONTHandYEAR

[Søren Dyhr]

Left ( MonthName ( Date ( Middle ( Input ; 5 ; 2 ) ; 1 ; 1 ) ) ; 3 ) & Middle ( Input ; 3 ; 2 )

Have you aready forgotten Shawns japanese trickery??

Hows about:

Replace (

Replace ( theString ; 5 ; 0 ; "+" ) ; 8 ; 0 ; "+" )

...into a date formatted field, where you format as desired.

--sd

[Genx]

You know I've never seen the replace function get used before!!

Anyway, that's quite cool:

GetAsDate(Replace( Replace ( "20071203" ; 5 ; 0 ; "+" ) ; 8 ; 0 ; "+" ))

Returns a properly formated date string.

[Søren Dyhr]

It does, but if the field already is in type date, would it be obvious (to me at least) to use the typecast going on anyway, and leave the formatting to the layout.

--sd

[Genx]

... so it would:

MonthName(Replace( Replace ( "20070203" ; 5 ; 0 ; "+" ) ; 8 ; 0 ; "+" ))

Will return february

Yay.

[Søren Dyhr]

You not quite getting it, why use the outer calc at all, except if you wish to poor it into a text field...

Use the menu point Format>Date (well see above)

Why, there is not a shred of doubt which way a date formatted field sorts while a hybrid text/number often proves counter intuitive to the user.

--sd

[Genx]

Because they need to produce a report in this case by year and month, and formatting is useless for that?

[Søren Dyhr]

Yes, I forgot!

--sd

[Rafita]

It depends on what you're looking for. If it is totals by month, just take Genx's field, modify the part of the month, and summarize by this field.

Date( Middle(YourTable::YourFie ld; 5 ; 2 ) ; [color:red]1; Middle( YourTable::YourField ; 1 ; 4 ) )

[Genx]

Hi Rafita,

You could do that... but:

If you take the first 6 numbers from an iso date string i.e. YYYYMMDD you get values like:

200703

200704

200602

200504

Which is a sortable number list... So if you took the first six numbers using the function Left( YourTable::YourField ; 6 ) you could dump that into a field and get the same results as you would if you first converted it into a date.

EDIT:

Hmmm, on second thought's maybe you're right. It would likely save the creation of an additional calculation field to represent the date as text (you could just format as Soren shows in his previous screenshot). Good thinking :P

Edited June 16, 2007 by Guest

[comment]

It's six of one or half-dozen of the other. You could put the converted date into the sub-summary part - as long as the day isn't showing, the label will be always correct.

My preference would be to convert the text string into a date once and for all, then use the result as the base for everything else. So the breakfield could be:

cDate - Day ( cDate ) + 1

and this could be used for the label as well. Seems more orderly that way.

Have you aready forgotten Shawns japanese trickery??

No - I just had no idea it would also work with GetAsDate(). Now that's completely undocumented!

[Genx]

I agree with you about converting the date first...

On a different topic, i'm not sure if this is completely undocumented, but seeing as we're talking about date functions:

I was for some reason stuffing around with MonthName(date) and it seems that it will return the correct month of the year, if you just give it x days...

E.g. MonthName(32) returns February, MonthName(366) this year returns January... well I've never seen it used so I thought I might mention it.

[comment]

MonthName() takes date as its argument. Therefore:

MonthName ( 32 ) = MonthName ( GetAsDate ( 32 ) ) = MonthName ( Date ( 2 ; 1 ; 1 ) )

MonthName(366) this year returns January

Hopefully, it should be clear now that MonthName(366) is NOT this year, but in the year 0002.

[Genx]

Hmmm, righto - sorry

Never occurred to me for some reason that it would have to do a date conversion before the call though this would probably be fairly obvious.

Edited June 16, 2007 by Guest