cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
723
Views
0
Helpful
2
Replies

CRS Editor - Retrieving Timestamp database field from SQL 2005

Andy Backus
Beginner
Beginner

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

1 Accepted Solution

Accepted Solutions

Clifford McGlamry
Rising star
Rising star

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

View solution in original post

2 Replies 2

Clifford McGlamry
Rising star
Rising star

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

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers