Converting Attain decimal datetime field

mscott

I'm exporting a Navision Attain 3.60 date field into SQL. The exported field looks like 63,255,909.417785. Does anyone know what SQL calculation I can run to convert it to a human readable date and time?

kine

Which tool you are using for exporting data from Navision? There must be some wrong conversion.

mscott

I'm using a Microsoft SQL DTS Transform Data Task. If I look at the field using Object Designer I see 63,255,909.417785. I get the same thing when I export it into SQL.

There are two other fields in that table that contain the date and the time but they are not always populated so I was hoping to export this field that is always populated. Apparently, it is being stored as a Navision internal format.

kine

If you mean fields 'TimeStamp' - it is timestamp not date and time...

Can you post table definition in Navision and which fields you want transfer?

Thanks

fb

The "Starting Date-Time" field in the "Prod. Order Line" table is an example of a Navision field that displays a date/time value, but is stored internally as a decimal. Here's a way to convert the decimal into a SQL datetime when your language of choice is T-SQL:

SELECT DATEADD(ms,CAST(pol."Starting Date-Time" * 1000000 AS bigint) % 86400000, 
       DATEADD(day, ROUND(pol."Starting Date-Time" / 86.4,1,1) - 723180, '1980-01-01'))
  AS StartingDateTime
  FROM dbo."<CompanyName>$Prod_ Order Line" AS pol

kjutte

The tip on how to convert the Decimal to DateTime was very useful. Here is the inverse method to convert DateTime to Decimal

The following SQL:
SELECT cast('2015-09-22 14:27:01.223' as DATE) as TheDate
,Cast('2015-09-22 14:27:01.223' as time) as TheTime
,((DATEDIFF(day,'1980-01-01',cast('2015-09-22 14:27:01.223' as DATE)) + 723180) * 86.4)
+ (DATEDIFF(ms,'0:00',Cast('2015-09-22 14:27:01.223' as time)) / 1000000.0) as TheDateAsDecimal
,DATEADD(ms,CAST(63610151.221223000 * 1000000 AS bigint) % 86400000,
DATEADD(day, ROUND(63610151.221223000 / 86.4,1,1) - 723180, '1980-01-01')) TheDecimalAsDate

Will result in: