Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query about using substring statement

Posted on 2011-03-01
3
Medium Priority
?
251 Views
Last Modified: 2012-05-11
Hope someone can make sense of this. Apologies in advance, the developer has left and I have no idea how he would do this type of thing. We have an application which has a config file which is where we can define what tables are imported into our database. Within the config file, we must define the remote field and local field.

We have a field which stores date and time of applications made via our webstie. The remote field which stores this data is date qnd time in the following format.

2011-03-01 13:59:00

Im just after the time part of the string.



In the config file, we already do some splitting of fields prior to importing. For example.

<ColumnName>substring(EmploymentDetailsJointApplicantTimeInJob,1,2)</ColumnName>
            <ColumnNameAlais>JointYearsAtJob</ColumnNameAlais>
            <GoldMineTable>CONTACT2</GoldMineTable>
            <GoldMineField>UA2JBYRS</GoldMineField>
          </GoldMindMapping>


          <GoldMindMapping>
            <ColumnName>substring(EmploymentDetailsJointApplicantTimeInJob,4,2)</ColumnName>
            <ColumnNameAlais>JointMonthsAtJob</ColumnNameAlais>
            <GoldMineTable>CONTACT2</GoldMineTable>
            <GoldMineField>UA2JBMTHS</GoldMineField>
          </GoldMindMapping>

The above entry in the config file splits time at job into years/months. Its stored in one field on the remote table like this 22/2.

The only thing Im not clear on is the substring bit. My dateTime field has the format like this 2011-03-01 13:59:00, Im just after the time bit so what would the substring bit need to obtaining the time part of the string.

I tried this but it didn’t do anything.

            <ColumnName>substring(dateTime,12,5)</ColumnName>
            <ColumnNameAlais>TimeOfApp</ColumnNameAlais>
            <GoldMineTable>CONTACT1</GoldMineTable>
            <GoldMineField>CREATEAT</GoldMineField>
          </GoldMindMapping>

Any ideas?
0
Comment
Question by:daiwhyte
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
Thommy earned 2000 total points
ID: 35011222
Substring function normally works like this...

function substring(<string>,<start number>,<length>)

e. g. substring('2011-03-01 13:59:00',6,5) returns '03-01'


To get the time part:

substring('2011-03-01 13:59:00',12,8) should return '13:59:00'

http://zvon.org/xxl/XSLTreference/Output/function_substring.html


Are you sure about your date/time format???
0
 
LVL 19

Expert Comment

by:Thommy
ID: 35011262
Another resource for substring in xml...

substring Function
http://msdn.microsoft.com/en-us/library/ms256054.aspx
0
 

Author Closing Comment

by:daiwhyte
ID: 35067924
Thank you.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Progress

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question