Convert String to Date (not CTOD())

Meeting place for R&R customers and clients to share tips and ideas. Post your questions, messages or problems here.
Post Reply
Ell_(Guest)
Posts: 5
Joined: Tue Oct 10, 2017 12:44 pm

Convert String to Date (not CTOD())

Post by Ell_(Guest) » Thu May 06, 2004 2:43 pm

Hi guys,____How can I convert a String value (YYYYMMDD) to a Date value (Regional Settings)?____Thanks,__Ell

kfleming
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

=> Convert String to Date (not CTOD())

Post by kfleming » Thu May 06, 2004 7:34 pm

CTOD(subs(stringfld,5,2) "/" (subs(stringfld,7,2) "/" subs(stringfld,1,4))____Where stringfld is the name of the field containing the YYYYMMDD______Kathleen__R&R Support

cal
Posts: 70
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Convert String to Date

Post by cal » Fri May 07, 2004 7:46 am

Thank you guys, Kathleen and Cal.____But it does NOT WORK. Maybe I did not explain properly in the__beginning. I have already used this solution before. It works if OS__has an American regional settings for the date format "MM/DD/YYYY".__And as soon as you run a report on a different settings:__"International: YYYY/MM/DD" or "European: DD/MM/YYYY" the function__CTOD() returns stars "****/**/**" or "**/**/****".____Looks like the R&R does not have a possibility to convert a String value__"YYYYMMDD" to a Date value regardless a Regional settings.____Who knows a trick, please let me know.____Thanks,__Ell____>CTOD(subs(stringfld,5,2) "/" (subs(stringfld,7,2) "/" __>subs(stringfld,1,4)) __>__>Where stringfld is the name of the field containing the __>YYYYMMDD __>__>__>Kathleen __>R&R Support ____

Ell_(Guest)
Posts: 5
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Convert String2Date(not CTOD())

Post by Ell_(Guest) » Fri May 07, 2004 10:52 am

Ell,____If you would explain where your program obtains the string, and its format in several cases, I would be willing to put further thought into a solution. What is the language being used in the application?__Is the string stored in a database or obtained from the OS at runtime by calling a function?________Cal

cal
Posts: 70
Joined: Tue Oct 10, 2017 12:44 pm

===> regional setting

Post by cal » Mon May 10, 2004 9:40 am

Cal,____I use the function LOSCOPE() & HISCOPE(). Almost all our reports have a scope by date and we print it. Before it was hardcoded "YYYY/MM/DD" but now I want to use a regional settings. And only for that. For example I have to know a month or a weekday name but I cannot use a string "YYYYMMDD" as a parameter for many functions (DAY(), MONTH() & etc.)____Thanks,__Ell

Ell_(Guest)
Posts: 5
Joined: Tue Oct 10, 2017 12:44 pm

====> RE: regional setting

Post by Ell_(Guest) » Tue May 11, 2004 4:45 pm

Ell:____If the previous solution doesn^t work, try using a + (plus sign) on each side the "/" characters, like this:____sub1 + "/" + sub2 + "/" + sub3____This concatenates (puts together, head to tail) each of the parts into one character string.____Cal

miklbrown
Posts: 2
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Convert String to Date

Post by miklbrown » Fri Aug 13, 2004 2:17 pm

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.__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))____

Post Reply