CODEWITHSUNDEEP
vbaexcelrecordsetteradata
logan
logan

Reputation: 8366

Define a VBA Recordset as String

When we define a record set in VBA & read the data from database, recordset converts its datatype to Table's column data type.

Dim rs as adobb.RecordSet 
Set rs = CmdSqlData.Execute() ' After this rs fields will be stored based on table's datatype

Is there any way to set the recordset itself to String and Perform, 

Set rs = CmdSqlData.Execute()

For Eg: if there is a timestamp value(which has integer value) in Database table definition, Recordsset sets its field datatype to timestamp.

The problem is , In database,

Time value is 12345 (Not date & time) but when record set reads it, it comes as For Eg: 23-06-2012 10:15:23

I wanted the value as 12345

Upvotes: 0

Views: 6037

Answers (3)

Rob Paller
Rob Paller

Reputation: 7786

Your SELECT statement will need to perform an explicit CAST for the Teradata TIMESTAMP to FLOAT (DATE is INTEGER)

SELECT CURRENT_TIMESTAMP()
     , CAST(CURRENT_TIMESTAMP() AS FLOAT) AS NumericTimeStamp
;

The rest of the VBA you should be able to work out as the Recordset will recognized the NumericTimeStamp as a FLOAT and not a TIMESTAMP.

Edit

The following SELECT statement will take an integer with a FORMAT clause and return a character data type as a result of the double cast:

SELECT CAST(CAST(1234 as INTEGER FORMAT '99:99:99') AS CHAR(8));

Have you tried to open your recordset with a SELECT statement that you explicitly CAST the column containing the time as a CHAR(8)?

Upvotes: 2

logan
logan

Reputation: 8366

I managed to convert this timestamp alone for now. But There are number of timestamp format available. I can not automate the SQL query to convert from different datatype to string. I require VBA to read the TERADATA VALUE as string What i did ,

    If Field_format = "99:99:99" then
    Sql = "Select Cast(Field_format as integer) from Mytable"
    Elseif Field_format = "99:99:99.999" then
    Sql = "Select Cast(Field_format as Float) from Mytable"
    Elseif Field_format = "99:99:99.999" then
    Sql = "Select Field_format from Mytable"
    End if
...
Set rs = CmdSqlData.Execute()

Kindly note there are so many datatypes available in teradata (Around 400+) , i can not convert everthying as above.

I just wanted to VBA to read Teradata values as string. Wondering There is no VBA Expert in StackOverflow

Upvotes: 1

KFleschner
KFleschner

Reputation: 499

If you're connecting to a SQL Server database, the timestamp datatype is a binary field used to determine if the record has changed or not (see How to convert SQL Server's timestamp column to datetime format) . This is different than an datetime field which holds, as it says, times and dates.

Returning a string from a recordset in ADO would like something like:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strSQL as string
Dim strSomething as string

strSQL = "SELECT fld1 FROM tbl1"

Set cn = New ADODB.Connetion
cn.ConnectionString = {your connection string here}
cn.open
rs.open strSQL, cn
While Not rs.EOF
    strSomething = rs!fld1
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

Upvotes: 0

Related Questions