CRS Editor - Retrieving Timestamp database field from SQL 2005

[Andy Backus]

Hello -

For the life of me, I cannot retrieve a database timestamp field from SQL 2005.

I have successfully used 'cast(Date as char(30)) as DateStr' to convert to string but the DBRead statement displays the original object only as java.lang.Timestamp.  Any posts regarding this topic usually require Java skill sets above my level (creating new java class for import,...).

There has to be an easy way to do this without being a Java programmer, yes?

Any help here would greatly be appreciated!

Thanks

Andy Backus

941.587.7199

[Clifford McGlamry · Accepted solution]

Andy,

I'm guessing you know that this isn't really a date/time value in MS SQL.  According to Microsoft: 

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

But to your question how to get the timestamp value out in a string...You'll want to use the convert function:

select convert(varchar(10),myTimestampField) from myTable

That will return the hex value in the column in a string format.  Also, when you retrieve the data, are you looking at the original table (which specifies the column is a timestamp), or did you create a dummy table with the columns named and formatted the way the data will be returned that UCCX can look at for the schema?  If it doesn't have the correct schema to look at, you'll have problems.

Cliff

please rate helpful posts

[Andy Backus]

Sorry for late response Clifford.  Found solution similar to what you have posted.  Thank you!