The previous article: #GPPT Parsing Returned SQL Data into Rows and Columns had some examples of how to convert string representations of various datatypes back to their native datatypes.

With GP Power Tools, it is a common technique to store additional data in the DUOS (Dynamic User Object Store) table (SY90000). However, the DUOS uses a 132 character string field to store the data and so datatype conversions in both directions will be needed.

[Edit] GP Power Tools now has a suite of MBS_SQL_Parse_Data Helper Functions to easily handle datatype conversions. It is recommended to use those rather than code them yourselves as shown in this example.

The easiest method to convert most datatypes to a string is to use the str() function in Dexterity. But this converts dates and times to strings based on the control panel regional settings and may not be consistent across countries.

For example:

• DD/MM/YYYY vs MM/DD/YYYY vs YYYY/MM/DD
• 14:30:00 vs 02:30:00PM vs 2:30:00PM vs 2:30:00P.M.

To ensure data integrity, it is vital to choose a format and stick with it when converting to and from string representations. I recommend using YYYY/MM/DD for dates and HH:MM:SS (24 hour time) for times.

The examples below show how to convert to and from the common datatypes used in Dexterity:

local string l_String;
local integer l_Integer;
local currency l_Currency;
local boolean l_Boolean;
local date l_Date;
local time l_Time;

{ Integer is simple }
l_String = str(l_Integer);

l_Integer = integer(value(l_String));


{ Currency is also simple }
l_String = str(l_Currency);

l_Currency = currency(value(l_String));


{ When Boolean stored as TRUE or FALSE }
if l_Boolean then l_String = TRUE_STRING; else l_String = FALSE_STRING; end if;

l_Boolean = (upper(l_String) = TRUE_STRING); 

{ When Boolean stored as 0 or 1 }
l_String = str(l_Boolean);

l_Boolean = (value(l_String) <> 0); 


{ Where Date in "YYYY/MM/DD" format }
l_String = pad(str(year(l_Date)),LEADING,str(0),4) + SLASH + pad(str(month(l_Date)),LEADING,str(0),2) + SLASH + pad(str(day(l_Date)),LEADING,str(0),2);

l_Date = mkdate(integer(value(substring(l_String, 6, 2))), integer(value(substring(l_String, 9, 2))), integer(value(substring(l_String, 1, 4)))); 


{ Where Time in "HH:MM:SS" format }
l_String = pad(str(hour(l_Time)),LEADING,str(0),2) + CH_COLON + pad(str(minute(l_Time)),LEADING,str(0),2) + CH_COLON + pad(str(second(l_Time)),LEADING,str(0),2);

l_Time = mktime(integer(value(substring(l_String, 1, 2))), integer(value(substring(l_String, 4, 2))), integer(value(substring(l_String, 7, 2)))); 

Note: If there are compile errors caused by the SLASH,  CH_COLON, TRUE_STRING or FALSE_STRING constants you can replace them with “/”, “:”, “TRUE”, “FALSE” respectively, but this will generate Literal String warnings. The Literal String warnings can be removed using the pragma(disable warning LiteralStringUsed); and pragma(enable warning LiteralStringUsed); pre-compiler commands.

Hope you find this information useful.

David

05-Jan-2023: Added note about MBS_SQL_Parse_Data Helper Functions.

This article was originally posted on https://www.winthropdc.com/blog.