Select SQL Server DatetimeOffset as .Net DateTimeOffset.Ticks

Question

I send a SQL query and receive FOR JSON .

SqlCommand comm = new SqlCommand(@"select top (1000) Date,[open],high,low,[close] from dbo.[foo"] where date > @backDate and date <= @Date  order by date FOR JSON AUTO", conn);

comm.Parameters.AddWithValue("@Date", _definition.dateFrom);
comm.Parameters.AddWithValue("@backDate", lowLimit);

string json = (string)comm.ExecuteScalar();

Debug.WriteLine(json);

/* this causes invalid deserialization */
CudaEngine.OHLC[] stream = JsonConvert.DeserializeObject<CudaEngine.OHLC[]>(json);

So the DateTimeOffset in json result looks like this:

[{"Date":"2019-07-30T00:29:00Z","open":1.241829000000000e+004,"high":1.245195000000000e+004,

My ohcl struct have to be like this with date as long.

public struct OHLC
{
    public long UTCdate;
    public double open;
    public double High;
    public double Low;
    public double Close;
}

How can I cast date directly from SQL statement to receive a .Net long either ticks or millisecond? How can I deserialize without the invalidCastException?

are you looking for this: https://stackoverflow.com/q/7386634 — Prasad Telkikar, Feb 15 '20 at 10:59
it will prolly hurt performance since its million rows i'm better store directly C# datetime as long ticks in database? — Zwan, Feb 15 '20 at 11:03
i find it strange i can't call a long from sql server even if its java long i can post process in GPU over having the sql server calculate all this stuff. — Zwan, Feb 15 '20 at 11:15

score 0 · Answer 1 · answered Feb 15 '20 at 11:57

Try following :

    public struct OHLC
    {
        private long _UTCdate { get; set; }

        public DateTime UTCdate
        {
            get { return DateTime.FromBinary(_UTCdate); }
            set { _UTCdate = value.ToBinary(); }
        }
        public double open { get; set; }
        public double High { get; set; }
        public double Low { get; set; }
        public double Close { get; set; }
    }

score 0 · Answer 2 · answered Feb 15 '20 at 17:00

This is simple to convert in the SQL query. The only tricky part (as always) is dealing with time zones.

If the SQL Server data type is datetimeoffset then you need to translate it to UTC time, then convert it to a datetime2, like this:

cast( [Date] at time zone 'UTC' as datetime2 )

If the SQL Server data type is a datetime or datetime2 and already represents UTC time then you don't need this conversion. And if they are datetime or datetime2 stored in some local time zone, you need to convert to a datetimeoffset in with the appropriate time zone, then translate to UTC as above. EG

cast( [Date] at time zone 'Central Standard Time' at time zone 'UTC' as datetime2 )

So assuming the SQL column is a datetimeoffset and you are converting to Unix Epoch Time, the query would look like:

var sql = @"
select top (1000) 
  datediff(second, '19700101', cast( [Date] at time zone 'UTC' as datetime2 ) ) Date,
  [open],
  high,
  low,
  [close] 
  from dbo.[foo"] 
  where date > @backDate 
  and date <= @Date  
  order by date 
  FOR JSON AUTO";

SqlCommand comm = new SqlCommand(sql, conn);

Select SQL Server DatetimeOffset as .Net DateTimeOffset.Ticks

2 Answers2