topic Re: SQL date/time import issue in FlexSim Forum

SQL date/time import issue

sunil_b2 — Wed, 18 Aug 2021 13:30:06 GMT

[ FlexSim 21.1.5 ]

When importing date/time data into global table from sql db , the data/time fraction is off by 420 years in fractions . Below picture shows the date, its value in excel and imported value via DB connection

see the attach model with copy paste and import. into global tables

Re: SQL date/time import issue

ryan_c10 — Wed, 18 Aug 2021 16:55:09 GMT

Hello @Sunil B2,

It actually turns out that your SQL imported value corresponds to 420 years, 228 days, 8 hours, 29 minutes, and 50.237 seconds. This is the amount of time that has elapsed since January 1, 1601 12:00:00 AM, which is the earliest time/date currently allowed by FlexSim. My guess would be that FlexSim produces the time/date format by adding the EventTime in seconds to that initial value or something along those lines.

Does this address your question?

Re: SQL date/time import issue

sunil_b2 — Wed, 18 Aug 2021 23:33:46 GMT

Ryan

No. Why did DB import offset by 420 years is the issue?

Re: SQL date/time import issue

ryan_c10 — Thu, 19 Aug 2021 16:05:54 GMT

I'm sorry for the misunderstanding. The difference between the Excel value and the SQL DB imported value is what date/time they are treating as zero and the units they are using to measure time.

Excel is using 12/30/1899 0:00:00 as its "zero-time" and is measuring the time since then in days.

FlexSim (and apparently the SQL DB, at least when imported) uses 1/1/1601 0:00:00 as its "zero-time" and measures the time since then in seconds.

I hope this helps!

Re: SQL date/time import issue

ryan_c10 — Thu, 19 Aug 2021 19:28:07 GMT

I'm attaching a model that just has a couple scripts in it. One of them converts a time into a date/time from Excel format, and the other in FlexSim format. But, they both output the same date.

Hope this helps!

SQL DateTime Convert.fsm

Re: SQL date/time import issue

Jeanette_Fullmer — Mon, 23 Aug 2021 19:45:08 GMT

Hi @sunil.b2, was Ryan Clark's answer helpful? If so, please click the red "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.

Re: SQL date/time import issue

sunil_b2 — Wed, 01 Sep 2021 16:23:41 GMT

It seems to be a bug in Flexsim .. When I import into excel from MSSQL the date/time come correctly without any offset.