November 17, 2010 at 2:40 pm
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
November 17, 2010 at 2:44 pm
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
November 17, 2010 at 2:51 pm
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.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 08:30:51.263
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-08-17 00:00:00.000
2010-11-17 16:50:38.717 40498 2010-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
November 17, 2010 at 3:14 pm
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
November 17, 2010 at 3:28 pm
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