Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

Alternative command to substring

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
daiwhyte
Asked:
daiwhyte
  • 2
  • 2
1 Solution
 
DuBoisCGCommented:
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
 
daiwhyteAuthor Commented:
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
 
DuBoisCGCommented:
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
 
daiwhyteAuthor Commented:
created new field and use datapart.

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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now