Insert datetime value into SAS table via C# .NET OLEDB SQL

Description: I have some C# code that creates a new table on my SAS server using OLEDB:

"CREATE TABLE SASUSER." + tableName + " (time DATETIME, value NUM)";

Then it uses a FOR loop to add some values from a .NET DataSet. time is a DateTime object, and value is just a Double. :

"INSERT INTO SASUSER." + tableName + " (time, value) VALUES (" + time.Ticks + " , " + value + ")"

It took some random开发者_运维知识库 guessing to find out that the time column would only accept numbers - not a typical date string (e.g. '06/26/11 13:24:11').

Problem: Even though I specified the time column to be DATETIME, only the date (mm/dd/yyyy) is showing up - not the time. e.x. If I replace 'time.Ticks' with 10 or 10.5 then 1/11/1960 is saved to the time value. It is interpreting the number as number of days since 1/1/1960, when it should be interpreting it as number of seconds since midnight 1/1/1960.

Notes: I am using OLEDB with SAS's SAS.IOMProvider provider. When I add the time value, I know I have to convert the ticks into SAS ticks (number of seconds since midnight Jan 1, 1960) - I just took out the extra calculations to make it easier to read.

Workaround: I'm using a template table on the SAS server which has the correctly formatted columns. I just make a copy of that table and copy my data into the template copy. This isn't ideal, but it will work as a quick fix.

I not sure about C# as i only familiar with VB.

In VB: INSERT INTO TABLE VALUES ('" & FORMAT(yourdatetime, "yyyy-MM-dd HH:mm:ss") & "')

Try refer to here and modify it into C#.

My simply guess: INSERT INTO TABLE VALUES ('" & String.Format("{0:yyyy-MM-dd HH:mm:ss}", yourdatetime) & "')