CTOD error with Regional Options

[Costa_G_(Guest)]

I have clients that work with different regional settings for their short date formats. Some clients use m/d/yy and others use d/m/yy. ____My application stores dates using the DTOS date format (YYYYMMDD) and I am using the CTOD function to get a date value in order to show it in the reports. ____Assuming that my variable is cDTOS and its value is "20040615" and the windows "Regional Options", "Short Date Format" is set to "m/d/yy"____I receive the correct date of 06/15/2004 with, __CTOD(SUBSTR(cDTOS,5,2) ^/^ SUBSTR(cDTOS,7,2) ^/^ SUBSTR(cDTOS,1,4))____But, if the "Short Date Format" is set to "d/m/yy", I receive a date of **/**/**** with the same instruction__CTOD(SUBSTR(cDTOS,5,2) ^/^ SUBSTR(cDTOS,7,2) ^/^ SUBSTR(cDTOS,1,4))____I cannot ask my clients to change their settings to support my report requirements and neither can I support two (or more) different date formats in my reports (over 150 and counting).____I know that I can simply print the date as YYYY-MM-DD but it doesn^t look as "sexy".____Is there any way of correctly converting a DTOS string to a correct date value?____Thank you

[kfleming]

You could create a conditional calculation to test if mm/dd/yyyy is an acceptable format. __Here is an abbreviated expression.____IIF(error(ctod("12/31/2001")),<dd/mm/yy format>, <mm/dd/yy format)____So if 12/31/2001 is an error then use the expression for dd/mm/yy otherwise use mm/dd/yy____Kathleen__R&R Support

[Costa_G_(Guest)]

Thank you very much for the quick and excellent answer.____I will create a user function and implement use this immediately!____For the future, could you consider providing such a function (eg. DTOS2Date(char) ) that would use a DTOS formatted character string (i.e. *YYYYMMDD") and return a date value, irregardless of the many "Regional Options" available for selection by the user? ____Thank you again__Costa Grigoropoulos

[kfleming]

You could create a user defined function to do just that using the expression that I sugggested. Then you would just need to give your users the UDF file so that the reports could use the new function.____Kathleen__R&R Support

[Costa_G_(Guest)]

Hi Kathleen____That^s exactly what I did (as I said I would in my last message), but the problem still remains as the following.____Due to the high number of internations date formats and customization possibilities that exist for Windows, the user function would have to test for each one of the multitude of date format possibilities. ____In order to distribute our products in the international market, it would be ideal if such an R&R function was available __(e.g. DTOS2Date(cDTOS) ) whereby we could give a DTOS date (YYYYMMDD) and receive a date value for the reports.____Again I thank you for your help__Costa Grigoropoulos

[miklbrown]

The only way that I have been successful in using the same R&R reports for customers with different international date formats is to use the following 3 UDF^s:____BegYear(date) - Returns First day of year based on a date.__Declaration: BegYear(D_CurrDate)__Expression:__SUBMONS((CurrDate - DAY(CurrDate) 1), MONTH(CurrDate)-1)____Separator(date) - Returns the separator used in the local date format. Created to correctly format dates sent as parameters by HOST. Used by STOD().__Declaration: Separator(D_CurrDate)__Expression:__IIF("/" $ DTOC(CurrDate), "/", IIF("-" $ DTOC(CurrDate), "-", IIF("." $ DTOC(CurrDate), ".", " ")))____STOD(date string:"YYYYMMDD") - Returns a date using the local windows date format. (Opposite of R&R function: DTOC(DATE,1))__Declaration: STOD(C_StrDate)__Expression:__CTOD(IIF(VAL(LEFT(DTOC(BEGYEAR(DATE())-1),2)) = 12, SUBSTR(StrDate,5,2) Separator(DATE()) RIGHT(StrDate,2), RIGHT(StrDate,2) Separator(DATE()) SUBSTR(StrDate,5,2)) Separator(DATE()) LEFT(StrDate,4))____