Newest functions Upload a function Leave a tip My account

There are 2,638 functions in this library.

ISO8601 ( source_date ; parameter )

Versatile function to covert and/or validate date inputs to YYYY-MM-DD text

  Be the first to rate this function Log in to vote

RealGrouchy - Show more from this author https://realgrouchy.blogspot.com

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/* ISO8601 ( source_date ; parameter ) by RealGrouchy. Posted to Brian Dunning's site 2026/15/04 ;-) This function takes date inputs in various specified or unknown formats and coverts it to YYYY-MM-DD text format, or can be used to validate whether a given input is in YYYY-MM-DD format. (When I need to write to a date field, I couple it with another function that converts YYYY-MM-DD to FM date format). It will accept inputs in the format YYYYMMDD, ####-##-##, #/#/####, ##/##/####, and many variants of these. The year must be the first or last component. If the input string contains a letter or a space, it will only evaluate the portion that comes before that space or letter. If the input string is invalid despite attempts to correct it (see 3rd bullet below), it will return the original value unaltered. PARAMETERS: • "date" if the input is a field formatted as a date, it parses the date according to the computer's localization (however inputted date format YYYY-MM-DD will override this, and invalid m and d will be swapped if the result is valid). If the result is invalid, it will return the original. When in doubt, use "date". • "ymd", "dmy", "mdy", etc. specifies the order of the date components. If the result is VALID, the function will use that. E.g. ISO8601 ( "2/9/2017" ; "dmy" ) will return 2016-09-02. Year must be first or last. • "ymd", "dmy", "mdy", etc. specifies the order of the date components. If the result is INVALID, it will attempt to swap the d and m, and if still invaild it will return the original string. E.g. ISO8601 ( "2/29/2017" ; "dmy" ) will return 2017-02-29, and ISO ( "2/29/2017" ; "ymd" ) will return the original "2/29/2017" back. • "unknown" will parse the date to the best of its ability (assuming four-digit year); if month and day components are ambiguous (i.e. both are under 12), it will use the computer's localization (i.e. in Canada, it will assume D/M/Y) • "day", "month" or "year" will return the specified portion of the date, so long as the date can be parsed in a valid manner. (Added 2016-12-07; revised 2017-02-09 because YYYYMMDD format returned ISO date instead of the component) • "validate" will return True (1) if the input is* already in the format YYYY-MM-DD (*or starts with, i.e. a timestamp starting in YYYY-MM-DD). It will return False (0) in all other cases, including if there is extra text or if MM is > 12 2023-04-10: Added correction for when submitted date is YYYY/MM/DD but parameter is specified as mdy */ Let ( [ /* If there is a space (e.g. timestamp) or a letter (e.g. UTC), only evaluate what comes before the first space or letter. */ source_date_nospace = If ( PatternCount ( source_date ; Filter ( Lower ( source_date ) ; " abcdefghijklmnopqrstuvwxyz" ) ) ; Left ( source_date ; Position ( source_date ; Left ( Filter ( Lower ( source_date ) ; " abcdefghijklmnopqrstuvwxyz" ) ; 1 ) ; 1 ; 1 ) -1 ) ; source_date ) ; /* Convert YYYYMMDD source date to YYYY-MM-DD */ /* 2023-04-10: Also YYYY/MM/DD */ source_date_withDividers = If ( ( Filter (source_date_nospace ; "1234567890" ) = source_date_nospace ) and ( Length ( source_date_nospace ) = 8 ) and ( Left ( source_date_nospace ; 4 ) ≤ Year ( Get ( CurrentDate ) ) + 99 ) and ( Left ( source_date_nospace ; 4 ) ≥ Year ( Get ( CurrentDate ) ) - 99 ) and ( Middle ( source_date_nospace ; 5 ; 2 ) ≤ 12 ) and ( Right ( source_date_nospace ; 2 ) ≤ 31 ) ; Left ( source_date_nospace ; 4 ) & "-" & Middle ( source_date_nospace ; 5 ; 2 ) & "-" & Right ( source_date_nospace ; 2 ) ; source_date_nospace ) ; /* Convert format parameter so I only have to change it here if I rename the parameter */ formatParameter = Lower ( parameter ) ; /* Sets a hyphen as the divider and replaces slashes and periods with hyphens for the purposes of evaluation. */ divider = "-" ; hyphenSource = Substitute ( source_date_withDividers ; [ "/" ; divider ] ; [ "." ; divider ] ) ; /* Determines the three components separated by the dividers */ bitA = Left ( hyphenSource ; Position ( hyphenSource ; divider ; 1 ; 1 ) - 1 ) ; bitB = Middle ( hyphenSource ; Position ( hyphenSource ; divider ; 1 ; 1 ) + 1 ; Position ( hyphenSource ; divider ; 1 ; 2 ) - ( Position ( hyphenSource ; divider ; 1 ; 1 ) + 1 ) ) ; bitC = Right ( hyphenSource ; Length ( hyphenSource ) - Position ( hyphenSource ; divider ; 1 ; 2 ) ) ; /* Determines the order of the components, if specified */ formatSpecifiesDMY = Length ( formatParameter ) = 3 and PatternCount ( formatParameter ; "d" ) = 1 and PatternCount ( formatParameter ; "m" ) = 1 and PatternCount ( formatParameter ; "y" ) = 1 ; OrderY = If ( formatSpecifiesDMY ; Position ( formatParameter ; "y" ; 1 ; 1 ) ; "unknown" ) ; OrderM = If ( formatSpecifiesDMY ; Position ( formatParameter ; "m" ; 1 ; 1 ) ; "unknown" ) ; OrderD = If ( formatSpecifiesDMY ; Position ( formatParameter ; "d" ; 1 ; 1 ) ; "unknown" ) ; /* Determines which of the three components is the year */ YYYY = /* Attempt to determine based on 4-digit component or for two digit numbers if it's greater than 31, or if order is specified */ Case ( ( Length ( bitA ) = 4 ) or ( GetAsNumber ( bitA ) > 31 ) or OrderY = 1 ; GetAsNumber ( bitA ) ; ( Length ( bitB ) = 4 ) or ( GetAsNumber ( bitB ) > 31 ) or OrderY = 2 ; GetAsNumber ( bitB ) ; ( Length ( bitC ) = 4 ) or ( GetAsNumber ( bitC ) > 31 ) or OrderY = 3; GetAsNumber ( bitC ) ; /* If order can't be determined, see if it is specified by the parameter (i.e. if year is two-digit) */ False ) ; /* 2023-04-10: Correct for a specified order that is incorrect where the submitted format is YYYY*MM*DD */ OrderM = If ( OrderY ≠ 1 and bitA = YYYY ; "unknown" ; OrderM ) ; OrderD = If ( OrderY ≠ 1 and bitA = YYYY ; "unknown" ; OrderD ) ; /* Get two variables for the month and date portions */ bit2A = Case ( YYYY = bitA ; bitB ; YYYY = bitC ; bitA ) ; bit2B = Case ( YYYY = bitA ; bitC ; YYYY = bitC ; bitB ) ; /* Determine which of those is the Month and which is the date */ MM = Case ( /* If the two are the same it doesn't matter */ bit2A = bit2B ; bit2A ; /* If one is over 12 and the other is under 12 it's obvious */ GetAsNumber ( bit2A ) > 12 and GetAsNumber ( bit2B ) ≤ 12 ; bit2B ; GetAsNumber ( bit2B ) > 12 and GetAsNumber ( bit2A ) ≤ 12 ; bit2A ; // I had ' & "b"' at the end of this and I haven't the slightest idea why. It didn't seem to cause problems. /* If order can't be determined, see if it is specified by the parameter */ OrderM ≠ "unknown" ; Case ( OrderM = 1 ; bitA ; OrderM = 2 ; bitB ; OrderM = 3 ; bitC ) ; /* If the date format is ####-##-##, safe to assume YYYY-MM-DD */ bit2A = bitB and divider = "-" ; bit2A ; /* If none of the conditions above, return false */ False ) ; DD = /* If order is not specified, attempt to determine */ Case ( /* If the two are the same it doesn't matter */ bit2A = bit2B ; bit2B ; /* If one is over 12 and the other is under 12 it's obvious */ GetAsNumber ( bit2A ) > 12 and GetAsNumber ( bit2B ) ≤ 12 ; bit2A ; GetAsNumber ( bit2B ) > 12 and GetAsNumber ( bit2A ) ≤ 12 ; bit2B ; /* If order can't be determined, see if it is specified by the parameter */ OrderD ≠ "unknown" ; Case ( OrderD = 1 ; bitA ; OrderD = 2 ; bitB ; OrderD = 3 ; bitC ) ; /* If the date format is ####-##-##, safe to assume YYYY-MM-DD */ bit2A = bitB and divider = "-" ; bit2B ; /* If none of the conditions above, return false to avoid misinterpreting ambiguous date format */ False ) ; /* If year is two-digit, convert to four-digit (can't do this earlier because the previous step depends on evaluating whether YYYY is equal to one of the bits) */ YYYY4 = Case ( Length ( YYYY ) = 2 and YYYY ≥ 66 ; 1900 + YYYY ; //no date in our organization should be less than 1967 Length ( YYYY ) = 2 ; 2000 + YYYY ; YYYY // If 4 digits already (or unknown number of digits!) leave as is. ) ; /* make sure none of the bits is empty (i.e. ####--#### ) and bitB isn't the year i.e. ##-####-## and the year portion is within 100 years of the current year */ /* This was previously in the "Calculation Result" calculation, but with the addition of the Day, Month and Year format parameters this must be calculated before evaluation (otherwise an invalid format would just return the full date) */ unclearFormat = ( IsEmpty ( bitA ) or IsEmpty ( bitB ) or IsEmpty ( bitC ) or /* if the year is the middle figure e.g. 07/2016/12, but avoid triggering on 12/12/12 */ ( YYYY = bitB and not ( YYYY = bitA or YYYY = bitC ) ) or ( DD = False ) or ( MM = False ) or ( Length ( bit2A ) > 2 ) or ( Length ( bit2B ) > 2 ) or ( YYYY4 > Year ( Get ( CurrentDate ) ) + 99 ) or ( YYYY4 < Year ( Get ( CurrentDate ) ) - 99 ) ) ; /* 2017-02-09: Not sure if I should be using original source_date here or source_date_nospace or source_date_withDividers. Prev version didn’t use hyphensource */ /* 2017-02-09: Not sure if I should be using original source_date here or source_date_nospace or source_date_withDividers. Prev version didn’t use hyphensource */ /* 2017-02-09: Not sure if I should be using original source_date here or source_date_nospace or source_date_withDividers. Prev version didn’t use hyphensource */ /* If the source date looks like a date and it is unclearFormat, we can try to interpret the month and day */ looksLikeDate = IsValid ( Day ( source_date ) ) ; DDf = Right ( 1000 + If ( unclearFormat and looksLikeDate ; Day ( source_date ) ; DD ) ; 2 ) ; MMf = Right ( 1000 + If ( unclearFormat and looksLikeDate ; Month ( source_date ) ; MM ) ; 2 ) ; YYYYf = If (unclearFormat and looksLikeDate ; Year ( source_date ) ; YYYY4 ) ; /* Prepare the result so that it can be validated if "validate" parameter was used to validate the input as being YYYY-MM-DD format */ DateResult = YYYYf & "-" & Right ( 1000 + MMf ; 2 ) & "-" & Right ( 1000 + DDf ; 2 ) ] ; /* END OF VARIABLE DEFINITION */ /* CALCULATION FOR RESULT FIXED? */ Case ( /* If format parameter wanted one component to be returned, do so. */ formatParameter = "day" ; DDf ; formatParameter = "month" ; MMf ; formatParameter = "year" ; YYYYf ; /* If parameter is Validate, see if the preliminary date result is the same as the Source Date (invalid/non-date input will be false) - input date of 2017-02-99 will return 2017-02-99, so need to validate DDf as well.*/ formatParameter = "validate" ; ( Filter ( DateResult ; "1234567890-" ) = source_date_nospace ) and ( DDf ≤ 31 ) ; /* If the format is unclear but it ISN'T formatted as a date, return the input string unchanged */ unclearFormat and not looksLikeDate ; source_date ; /* Otherwise return the date */ DateResult ) // Case ) // end Let2 Click to copy

This function takes date inputs in various specified or unknown formats and coverts it to YYYY-MM-DD text format, or can be used to validate whether a given input is in YYYY-MM-DD format. (When I need to write to a date field, I couple it with another function that converts YYYY-MM-DD to FM date format). This is useful when receiving date inputs from interfaces that don't validate or standardize the date format, or from ones that consistently supply the date in a particular non-ISO format.

It will accept inputs in the format YYYYMMDD, ####-##-##, #/#/####, ##/##/####, and many variants of these. The year must be the first or last component.

If the input string contains a letter or a space, it will only evaluate the portion that comes before that space or letter.

If the input string is invalid despite attempts to correct it (see 3rd bullet below), it will return the original value unaltered.

 

Comments

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 22 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: