Datetimme Stamp

  • I have many tables in a third party software owned by marel(www.marel.com) where dates are stored in digits and decimal number format. I am struggling to convert those numbers into normal datetime stamp using SQL server 2005 for the reporting purpose, but no luck. I have attached a sheet for your review.

    Any help would be appreciated.

    Thanks

  • Most of the time when dates are stored that way, the number before the decimal is the number of days from a starting date, and the number after the decimal is a number of units of time since midnight on that date.

    So, for example, if the starting date is 1 January 1900, then 1.0 would be midnight on 2 January 1900, and 2.0 would be 3 Jan, and so on.

    SQL Server datetime uses 1/300th of a second since midnight for the part after the decimal.

    Try something like that.

    Or, perhaps better yet, contact their tech support and ask them how the column works.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • looks like the dates were simply converted to decimal to me:

    /*--results

    (No column name)(No column name)(No column name)

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 08:30:51.263

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 00:00:00.000

    2010-11-17 16:50:38.717404982010-08-17 16:17:17.087

    */

    WITH MyCTE AS (

    SELECT 40405 AS DecDate UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405.35476 UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405 UNION ALL

    SELECT 40405.67867)

    Select

    getdate(),

    CONVERT(decimal,getdate()),

    CONVERT(datetime,DecDate)

    FROM MyCTE

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Given the data points for the date, it looks like the base date is 1899-12-30. The time is harder to determine, because you haven't given us enough data points. 0.25666 is just over 1/4 and would correspond to 06:09:35.423 AM, but 15:31 is well over 1/2, so there isn't a direct correspondence between those, so it must be using some other method to code the time.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lowell is probably correct, but there is a possibility that the numbers in your spreadsheet mean the date portion before the decimal and the number of seconds after the decimal point. I don't know why the dates are stored like this, but it makes sense just by looking at your sample data (the number of digits is 5 or less and there are no decimal parts greater than 86399). This kinda mimics how the datetime values are stored in the database: 8 bytes total, left int (4 bytes) stores the number of days from zero date and the right int (4 bytes) stores the number of ticks from midnight (tick is about 3 milliseconds, which explains why the rightmost digit of the millisecond part of any valid datetime value can take only 3 possible digits, namely 0, 3 and 7).

    Anyway, if this is the case then you have to use the int part of your value and add it to the zero date to get the date part and then add the number of seconds to it equal to the decimal part * 100000.

    Using the data in your spreadsheet:

    declare @t table

    (

    OrderNo int,

    CreatedTime decimal(10, 5),

    DispatchedTime decimal(10, 5)

    );

    insert into @t values (35652, 40405,40411.25666 );

    insert into @t values (35654, 40405, 40406.72558);

    insert into @t values (35818, 40405, 40408.63655);

    insert into @t values (35864, 40405.35476, 40411.67777);

    insert into @t values (35904, 40405, 40409.72241);

    insert into @t values (35926, 40405, 40410.63184);

    insert into @t values (35927, 40405, 40410.63031);

    insert into @t values (35928, 40405, 40410.62948);

    insert into @t values (35929, 40405.67867, 40410.6987);

    -- here is the sample select converting the decimals based on the logic

    -- I described (I don't know whether my guess is correct though

    select

    OrderNo,

    CreatedTime,

    dateadd(second, (CreatedTime - cast(CreatedTime as int))

    * 100000, dateadd(day, cast(CreatedTime as int), 0)) CreatedDateTime,

    DispatchedTime,

    dateadd(second, (DispatchedTime - cast(DispatchedTime as int))

    * 100000, dateadd(day, cast(DispatchedTime as int), 0)) DispatchedDateTime

    from @t;

    -- results:

    OrderNo CreatedTime CreatedDateTime DispatchedTime DispatchedDateTime

    ------- ------------ ----------------------- -------------- -----------------------

    35652 40405.00000 2010-08-17 00:00:00.000 40411.25666 2010-08-23 07:07:46.000

    35654 40405.00000 2010-08-17 00:00:00.000 40406.72558 2010-08-18 20:09:18.000

    35818 40405.00000 2010-08-17 00:00:00.000 40408.63655 2010-08-20 17:40:55.000

    35864 40405.35476 2010-08-17 09:51:16.000 40411.67777 2010-08-23 18:49:37.000

    35904 40405.00000 2010-08-17 00:00:00.000 40409.72241 2010-08-21 20:04:01.000

    35926 40405.00000 2010-08-17 00:00:00.000 40410.63184 2010-08-22 17:33:04.000

    35927 40405.00000 2010-08-17 00:00:00.000 40410.63031 2010-08-22 17:30:31.000

    35928 40405.00000 2010-08-17 00:00:00.000 40410.62948 2010-08-22 17:29:08.000

    35929 40405.67867 2010-08-17 18:51:07.000 40410.69870 2010-08-22 19:24:30.000

    Oleg

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply