Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Alternative command to substring

Posted on 2011-03-10
4
Medium Priority
?
401 Views
Last Modified: 2012-05-11
Hi all,

I posted this question below last week and it worked on some fields but not others. I think he substring command only works on certain field types like varchar. The field Im trying to use the substring command on is smalldatetime data type and the substring command doesnt work, is there a similar command which can be used on the field so I can extract specific data out of the smalldatetime field?

Thanks in advance.



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
  • 2
4 Comments
 
LVL 2

Expert Comment

by:DuBoisCG
ID: 35101034
Is the name of the fiield actually "datetime"?

<ColumnName>substring([FieldName],12)</ColumnName> seems correct.

I'm not sure if your file is W3C compatible you could try
format-time([FieldName], "[h]:[m01]:[s01] [Pn]"

If this is a SQL import then you might be able to use the SQL datepart function.
0
 

Author Comment

by:daiwhyte
ID: 35106436
THe program does import data into our sql table so its possible and yes, the field name is called dataTime. When I try using the substring command with this, I get the following error message.



System.Data.SqlClient.SqlException: Argument data type smalldatetime is invalid for argument 1 of substring function.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
   at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateO
bject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
   at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
   at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
   at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables,
Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior
behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at DBPoler.Handler.Work() in
0
 
LVL 2

Accepted Solution

by:
DuBoisCG earned 2000 total points
ID: 35167598
Did you try
<ColumnName>format-time(dataTime, "[h]:[m01]:[s01] [Pn]</ColumnName>

You could also create another field to import the date into and then perform a datepart after the import to get your time correct.
0
 

Author Closing Comment

by:daiwhyte
ID: 35176386
created new field and use datapart.

Thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
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 …
Screencast - Getting to Know the Pipeline

610 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