SQL date/time import issue

[sunil_b2]

[ 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

[ryan_c10 · Accepted solution]

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?

[sunil_b2]

Ryan

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

[ryan_c10]

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!

[ryan_c10]

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

[Jeanette_Fullmer]

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.

[sunil_b2]

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