Adding Date and Time together from both DateTime fields

[RL_Boston]

This must be a simple one but I couldn't find a post.

I have
Date Column : 2011-02-24 00:00:00.000
Time Column : 1900-01-01 15:27:06.000

Using FM, How do I combine or add these two fields to get 2011-02-24 15:27:06.000 ?

Thanks!

[AussiePete2011]

Hi there

Off hand I'd suggest using substring and concatention
E.g. http://www.cognoise.com/community/index.php?topic=8619.0

https://www-304.ibm.com/support/docview.wss?uid=swg21367684

substring
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2"
characters or to the end of "string_exp" if "integer_exp2" is omitted. The first character in
"string_exp" is at position 1.
Syntax
substring ( string_exp , integer_exp1 [ , integer_exp2
] )
Example
substring ( 'abdefg', 3, 2)
Result: 'de'

Unless you push this back to the database and manipulate the data there
Cheers
Peter B

[RL_Boston]

Hi Peter,

Thanks for the response. Will the substring save the result into a datetime format?

I had wanted to do this calculation on the DB layer but I have not mastered IBM's Websphere and considering the data is extracted from a Unicode CSV file. Currently converted from Unicode Varchar to DateTime.  I think I would have had to make more steps and columns to achieve the desired result. Any other suggestions would be appreciated!

Thanks!

[AussiePete2011]

Hi there

No not unless you then cast the resulting string back to a date.
Its a bit awekard but thats the only way within FM to make this type of change

So are you using DB2?  If its DB2 you would use the DB2 Console to access the DB2 instance and then use the query tool to administer the db schema.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.cc.doc/db2_udb/intro.htm

DB2 substr ()
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000854.htm

Let me know
Cheers
Peter B

[RL_Boston]

Ended up just adding the two time fields in the ETL using SQL statement.