locked
DateTime Conundrum RRS feed

  • Question

  • User325035487 posted

    I got access to a third party database in MS Sql 2014 where date and datetime is stored as 18 digit bigint

    See the table below. When I apply the code in column name as Web Grid I can convert to the date time. (values in red are generated in grid by the code)

    LocalDate Time new DateTime(item.Time)).AddHours(3).ToString("dd-MMM-yy HH:mm")
    131969088000000000 131969521243673000 2019-03-13 15:02 The same formula without .AddHours(3) applied to column date will give me 2019-03-13
    131969088000000000 131969521123675000 2019-03-13 15:01
    131969088000000000 131969519852728000 2019-03-13 14:59
    131969088000000000 131969519692725000 2019-03-13 14:59
    131969088000000000 131969518172715000 2019-03-13 14:56

    When I try to filter the Sql Query using the code given in https://stackoverflow.com/questions/3650320/convert-from-bigint-to-datetime-value i copied below, I am getting an overflow error as that example is for a 13 digit bigint field and by database has 18 digits.

    select A,B,C,dateadd(s, convert(bigint, [Events.Time]) / 1000, convert(datetime, '1-1-1970 00:00:00')) AS Date from Events WHERE Date=@0

    Error messages I am getting when i try to filter using above query or variations

    • Arithmetic overflow error converting expression to data type int (I also tried to divide by 100000000 instead of 1000 but I am getting dates as 2011-10-29 something.)

    Any idea how to select all events from a particular date using MS-SQl 2014 Query. Any one knows what kind of data is stored in the LocalDate/time Column? How can C# cast as new Date work if its 18 digits

    Wednesday, March 13, 2019 12:32 PM

Answers

  • User325035487 posted

    Ok. I seem to have stumbled on to a sixteen year old post which gave me the solution

    DECLARE @dt AS bigint
    SET @dt = 131969521123675000
    SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
    DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

    This gives accurate date. But the time zone is less by three hours.

    The OP with solution says this type is it's a Win32 FILETIME structure: that stores the date as 100-nanosecond intervals since 1601

    Solution here - https://www.sqlteam.com/forums/topic.asp?topic_id=26544

    I do not know how to add the 3 hours time zone difference to this. But at least I can work on this using the date only column

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 6:24 PM
  • User-893317190 posted

    Not sure what  your 131969088000000000 represents, but you could convert the long value to int first and then pass to the adddate function.

    Such as

    select dateadd(DAY,  131969088000000000/10000000/60/60/12/3/2/3, convert(datetime, '1970-01-01 00:00:00')) AS Date 

    If in your case 3 hours time zone difference appears, then you could use dateadd to add or minus another threes in your computed value.

    Another way, for example, Date=@param , pass long value to your ado.net parameter not DateTime and in your sql , use number to filter instead of Datetime.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 14, 2019 4:29 AM

All replies

  • User753101303 posted

    Hi,

    This is the number of milliseconds since 1/1/1970 ?

    Even with actual dates my approach is to use :

    WHERE MyColumn>=@DayStart AND MyColumn<@NextDayStart so that I select all values during a particular day regardless of the hours and still use a possible index on MyColumn.

    I would use the same approach expect that dates will need to be converted back to this kind of big int value. To do that I would likely create two functions to convert back and forth (may in multiple steps ie computing from this bigint values how many days and miliseconds it is so that hopefully it fits inside an int).

    This is the product shown in the other post or something else ?

    Wednesday, March 13, 2019 1:10 PM
  • User325035487 posted

    This is the number of milliseconds since 1/1/1970 ?

    If it was, the stack overflow code should work. But their example shows a 13 digit bigint.

    mine is a DB for a software for event log which stores as 18 digit bigint column. i copied actual values from the db columns above in the first two columns of table. I am able to convert those using the c# code in the column header and it works correctly.

    But if i want to filter from DB.. there are about 700,000 rows, i think it would not be efficient to select all rows and then filter using c#. But i can find any way to filter using a sql query as all examples in public forum uses a 13 digit bigint.

    For example, the value 131969521123675000 translated using c# code new DateTime(131969521123675000 )).AddHours(3).ToString("dd-MMM-yy HH:mm") worked perfectly to give 2019-03-13 15:01

    My question is how to filter the data before generating my web grid. - I want to select all the rows on a daily basis for example, or create a pivot query for event type per day and so on

    Wednesday, March 13, 2019 6:05 PM
  • User325035487 posted

    Ok. I seem to have stumbled on to a sixteen year old post which gave me the solution

    DECLARE @dt AS bigint
    SET @dt = 131969521123675000
    SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
    DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

    This gives accurate date. But the time zone is less by three hours.

    The OP with solution says this type is it's a Win32 FILETIME structure: that stores the date as 100-nanosecond intervals since 1601

    Solution here - https://www.sqlteam.com/forums/topic.asp?topic_id=26544

    I do not know how to add the 3 hours time zone difference to this. But at least I can work on this using the date only column

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 6:24 PM
  • User-893317190 posted

    Not sure what  your 131969088000000000 represents, but you could convert the long value to int first and then pass to the adddate function.

    Such as

    select dateadd(DAY,  131969088000000000/10000000/60/60/12/3/2/3, convert(datetime, '1970-01-01 00:00:00')) AS Date 

    If in your case 3 hours time zone difference appears, then you could use dateadd to add or minus another threes in your computed value.

    Another way, for example, Date=@param , pass long value to your ado.net parameter not DateTime and in your sql , use number to filter instead of Datetime.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 14, 2019 4:29 AM