Jump to content
k2qo

Convert 20060612 to 6/12/2006 HELP

Recommended Posts

k2qo

Hi Gang,

 

I am new to DB stuff and have inherited a lead list for a new home builder. I have the layouts up and running, sorting is working as required and my printed output looks good. My last task is to get the dates formatted correctly.

 

BUT, I cannot figure out how to convert thousands of dates in YYYYMMDD format to MM/DD/YYYY format. My new date fields have input masks to allow ten digit text string entry and the desired text field look.

 

Any ideas?

 

Thanks,

Mark

Clarence, NY

Share this post


Link to post
Share on other sites
Maarten Witberg

so the current date field is essentially a number field? Filemaker has fields with date format, which are recognised as dates, and upon which date calculations can be performed. Date fields can be formatted to display the date entered in many different ways (see menu bar-> format -> format date when the date field is selected).

You could convert the number field to a new date field by

 

Date ( Middle(OldDateField;5;2) ; Right(OldDateField;2) ; Left(OldDateField;4) )

 

do you want to keep using the old field? Depending on the answer, you can make the above a new calculated date field. If not, you'd have to create a new date field and use the above in a replace field contents (by calculation).

In that case, make a backup before proceeding.

Share this post


Link to post
Share on other sites
k2qo

kjoe,

 

Playing around with variations of your suggested formula has yielded a text field that looks like:

 

2006/04/25

 

when the actual date in the original field was

 

20060425

 

BUT, I need to have 04/25/2006. Any idea how to "turn it around"?

 

Thanks,

Mark

Share this post


Link to post
Share on other sites
Maarten Witberg
yielded a text field

I think you should set the calculation result to date. The calculation I provided is based on the Date( month ; day ; year ) function. Essentially it takes three number values and converts them into a true date, which can be displayed in a date type field or in a calculation with date result.

My function parses out your number.

You say your number is YYYYMMDD

 

So with Middle(number;start;number of characters) I isolate the month:

middle ( 20060425 ; 5 ; 2 ) =04

left ( 20060425 ; 4) =2006

right(.... you get the idea.

 

So with this calc you have converted a number string that looks like a date, to a true date string.

That makes it possible to apply various date formats at display level. You can display year, month and date in any order, even show the dayname and/ or monthname, and using a variety of separators. Just select the field, go to menu bar->format->date... and follow the steps in the dialog.

Share this post


Link to post
Share on other sites
chapeau_melon

Tx a lot,

 

I found a similar solution like :

Let([

Parts=Substitute(

YourDateField; ["/";" "]; ["-" ; " "]);

Day=RightWords(Parts;1) ;

Month=MiddleWords(Parts;2;1 );

Year=LeftWords(Parts;1)];

Day &"/"& Month &"/"& Year)

 

or;

 

Right ( yourField ; 2 ) &"/"& Middle ( yourField ; 6 ; 2 ) &"/"& Left ( yourField ; 4 )

 

 

Kind regards and tx for the help !!

Share this post


Link to post
Share on other sites
k2qo

chapeau_melon,

 

I ended up using your formula. I figured it out from a combination of reading Pogue's book and some messages posted here.

 

kjoe,

 

Sorry, but the formula you posted gives me bizarre dates with lots of slashes. In fact, the calculation result is not intelligible. Hmmm....

 

I admit that I'm a newbie too all of this, but things are looking up. My next challenge is to get the db polished up with validations for the fields and make the last few scripts so even a computer novice can handle input. Or course, I may find that I need to get Apple Remote 3 running ASAP!

 

Thanks to all,

Mark

Share this post


Link to post
Share on other sites
Maarten Witberg
Right ( yourField ; 2 ) &"/"& Middle ( yourField ; 6 ; 2 ) &"/"& Left ( yourField ; 4 )

I'm pretty sure that 6 should be a 5.

I don't understand why my calc does not work for you. I checked again, it works fine in my test file. I must admit I didn't know that merely parsing the string into a date format without using the Date() function works also to get a string that filemaker recognises as a date.

There is a difference in behaviour though. for instance, 20070229 would result in a "?" , whereas using Date() it would return 03/01/2007 (no leap year in 2007). I guess it doesn't matter if all original date strings are valid, existing calendar dates.

 

The other function by chapeau_melon should work on text fields not on number fields.

Share this post


Link to post
Share on other sites
comment
I didn't know that merely parsing the string into a date format without using the Date() function works

It works only when the parsed string matches the file's date format.

Share this post


Link to post
Share on other sites
Kane

How about dropping the entire idea of trying to parse the string as text and use some simple arithmetic instead, ie with a bunch of mod() and div(). Wouldn't that work better?

Share this post


Link to post
Share on other sites
Maarten Witberg

I'd like to see how you'd do that. Say you have a number string with the value 20,070,412 . How does that become april 12 of the year 2007 using arithmetic?

Share this post


Link to post
Share on other sites
comment

"20,070,412" is not a number. And perhaps the point is that neither is "20070412" (semantically). Although you COULD extract 2007 from it by:

 

Div ( yyyymmdd ; 10000 )

Share this post


Link to post
Share on other sites
Maarten Witberg

Right, I was under the impression that "some simple arithmetic" would mean, a nifty calculation that would return a date from a string of digits which, to my understanding, still just is a large number that bears no relation to the actual date it represents, only by convention.

What you describe as "extracting" to me sounds like parsing with other functions. I don't know if that would be more or less efficient than using the text parsing functions available.

Share this post


Link to post
Share on other sites
Kane

Hrm. Sheepishly, I have to admit you're right. It is ultimately just parsing using integer functions instead of string functions.

 

On an off note, I vaguely recall encountering an integer date format maybe in excel or something else, based on seconds? That'd be a way to do it via arithmetic.

Share this post


Link to post
Share on other sites
comment

I agree. There is no computational advantage in this, and, as a rule, I prefer using text functions when dealing with text.

 

The fact that this is text is easily shown by the fact that applying any basic numeric operation to it is rather meaningless - similar to a phone "number", for example.

Share this post


Link to post
Share on other sites
comment

Filemaker dates (and times and timestamps) are integers too. They are only displayed as dates (and times and timestamps). Apply GetAsNumber() to any one of these to see it.

Share this post


Link to post
Share on other sites
jvamos

I am very new to filemaker. I was wondering if anyone has a tip on how to "zero fill" a number so that "1" as an entry displays "01" through validation but "10" returns "10".

The Lynda course is great to get started but it's hard to know where to start to apply all of the features of FileMaker. This forum is really my goto at this point.

Share this post


Link to post
Share on other sites
Maarten Witberg

right ( "00" & yourvalue ; 2 )

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use