Query about using substring statement

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?
daiwhyteAsked:
Who is Participating?
 
ThommyConnect With a Mentor Commented:
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
 
ThommyCommented:
Another resource for substring in xml...

substring Function
http://msdn.microsoft.com/en-us/library/ms256054.aspx
0
 
daiwhyteAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.