SQL Datetime conversion

[Martin.Griffiths]

Hi,

I'm having problems converting a SQL datetime column into a DIAdem timestamp in the correct format - I'm getting the month & days mixed up. I.e my logged data is a series of rows for 4th, 5th & 6th October, but imports to DIAdem as 10th April, 10th May, 10th July....

I've tried various methods, the latest was Brad's code below using ADO to import the 'Timestamp2' column. I've also tried adjusting DIAdem's time display setting, this doesn;t make any difference.

' needs to be set to a valid ADO connection string for data base

ConnectionStr =

"DSN=dc; Uid=; Pwd=;"

' construct the SQL query to execute

Table   =

"HMI_Data"

ColStr  =

"Timestamp2,PT_1_004,PT_1_085"

'CondStr = " WHERE sensor_id=2"

'SortStr = " ORDER BY Timestamp2"

QueryStr =

"SELECT " & ColStr & " FROM " & Table

'QueryStr = "SELECT " & ColStr & " FROM " & Table & CondStr & SortStr

MsgBox

QueryStr

' Connect to the data base

Set

ADO = CreateObject("ADODB.Connection")

ADO.Open ConnectionStr

' Execute the query and import the resulting data records into a VBScript variable

Set

RecordSet = ADO.Execute(QueryStr)

RowVals = RecordSet.GetRows()

ChanNames =

Split(ColStr, ",")

' Send the resulting data records from the query to new channels in the Data Portal

Call

DataDelAll

Call

GroupCreate(Table)

Call

GroupDefaultSet(GroupCount)

Channels =

ArrayToChannels(RowVals, ChanNames, 1)

' Disconnect from the database and output the query used and any errors

ADO.Close

How can I adjust / edit the time format when I import it?

Thanks... Martin

[Brad_Turpin]

Hi Martin,

Usually a data base column that is of datatype DateTime loads flawlessly into a DIAdem channel of datatype DataTime with the code you posted.  What datatype is that "Timestamp2" column you're loading?  What datatype is the resulting DIAdem channel-- DateTime or String?  If you're getting a String channel in DIAdem because you have a String column in the data base, then I can help you convert the resulting String channel to a correct DateTime channel.

Brad Turpin

DIAdem Product Support Engineer

National Instruments