• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

convert a number string to a date

DMHERSH

Member
Hi

I a beginner in using BI Publisher, I have a number string in my XML which looks like 20161130 I need to create a date format 30/11/2016 from it, in the past I have received the XML in date format so was able to get my date using the template builder. The code I have in the field currently is
<?_025_Date_End_ID4?> what should the code look like? I'd appreciate a helping hand even if it just points me to the place I can find the correct code format
The same goes for a number string 1402 that needs to changed to 14:02

Thank you in advance

David
 

sheeba

Reputable Poster
Try this :

On the word template using word plug in

1. Double click on the date field and it will take you to properties ; define it as a date field and choose the format
2. Double click on the time field and on the properties ; define it as time and choose the format.

This should work . Let me know if it did for you.
 

DMHERSH

Member
Thanks Sheba, your answer is correct and does work when the number string is set up a date in the XML, but I am getting a string of numerals and need convert them to the date format see the string below:
<PageHeaderNumber>1</PageHeaderNumber>
<_012_ID2>1012</_012_ID2>
<_012_FilePath_ID1>/OPENFRMT/</_012_FilePath_ID1>
<_024_ID5>1024</_024_ID5>
<_024_Date_Start_ID3>20160101</_024_Date_Start_ID3>
<_025_ID6>1025</_025_ID6>
<_025_Date_End_ID4>20160630</_025_Date_End_ID4>
<_026_ID7>1026</_026_ID7>
<_026_Date_of_Creation_ID8>20161208</_026_Date_of_Creation_ID8>
<_027_Time_of_Creation_ID10>1837</_027_Time_of_Creation_ID10>
<_027_ID9>1027</_027_ID9>
</A000_S4>

I have used the
 

xxpeta

Active Member
Hi,

try to use concat and substring...<?xdofx:substr(026_Date_of_Creation_ID8,7,2)?>/<?xdofx:substr(026_Date_of_Creation_ID8,5,2)?>/<?xdofx:substr(026_Date_of_Creation_ID8,0,4)?>
 
Discover how to build no-code data integrations and business process automations.

DMHERSH

Member
Thank you xxpeta, but on trying this all I get is the slashes none of the numbers are coming through.
 

xxpeta

Active Member
just corrected positions: <?xdofx:substr(026_Date_of_Creation_ID8,9,2)?>/<?xdofx:substr(026_Date_of_Creation_ID8,6,2)?>/<?xdofx:substr(026_Date_of_Creation_ID8,1,4)?>
 

jileto

Well Known Member
Maybe using this is clearer...

<?xdoxslt:right(_026_Date_of_Creation_ID8,2)?>/<?xdofx:substr(_026_Date_of_Creation_ID8,5,2)?>/<?xdoxslt:left(_026_Date_of_Creation_ID8,4)?>

Rightmost 2 chars for the day-of-month; slash, then middle 2 digits starting from position 5 (the 1st 4 are the year) for the month; slash, then 1st 4 digits from the left for the year.
 
Top