question

Sunil B2 avatar image
0 Likes"
Sunil B2 asked Sunil B2 answered

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


1629292672737.png


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

FlexSim 21.1.5
flexsim 21.1.5connectivity
1629292672737.png (4.8 KiB)
ms-sql-import-issue.fsm (27.7 KiB)
· 1
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jeanette F avatar image Jeanette F ♦♦ commented ·

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 Likes 0 ·

2 Answers

Ryan Clark avatar image
0 Likes"
Ryan Clark answered Ryan Clark commented

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
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Sunil B2 avatar image Sunil B2 commented ·
Ryan

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

0 Likes 0 ·
Ryan Clark avatar image Ryan Clark Sunil B2 commented ·

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 Like 1 ·
sql-datetime-convert.fsm (25.0 KiB)
Ryan Clark avatar image Ryan Clark Sunil B2 commented ·
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 Likes 0 ·
Sunil B2 avatar image
0 Likes"
Sunil B2 answered

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

5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.