SQL date/time import issue

Question

question

Sunil B2 asked Aug 18, '21 Sunil B2 answered Sep 1, '21

SQL date/time import issue

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

Software Version:

FlexSim 21.1.5

flexsim 21.1.5 connectivity

1629292672737.png (4.8 KiB)

ms-sql-import-issue.fsm (27.7 KiB)

· 1

Jeanette F ♦♦ commented · Aug 23, 2021 at 07:45 PM

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.

0 ·

Answer 1 · 2021-08-18T16:55:09Z

Ryan Clark answered Aug 18, '21 Ryan Clark commented Aug 19, '21

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?

· 3

Sunil B2 commented · Aug 18, 2021 at 11:33 PM

Ryan

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

0 ·

Ryan Clark Sunil B2 commented · Aug 19, 2021 at 07:28 PM

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

1 ·

sql-datetime-convert.fsm (25.0 KiB)

Ryan Clark Sunil B2 commented · Aug 19, 2021 at 04:05 PM

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!

0 ·

Answer 2 · 2021-09-01T16:23:41Z

Sunil B2 answered Sep 1, '21

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

question