Solved

Alternative command to substring

Posted on 2011-03-10
4
392 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
  • 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 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

810 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