Change column type from int to Datetime

Question

question

kauan pedroso asked Feb 17 2023 at 6:31 PM Jason Lightfoot commented Feb 27 2023 at 1:58 PM

Change column type from int to Datetime

hey guys

I need some help, I have a table that the data comes from a SQL SERVE database but in the date columns the values are coming in seconds. How can I do to change this?

as is in FlexSim

as is in SQL SERVE

Software Version:

FlexSim 21.2.4

global table sql query sql serve

1676657986117.png (3.0 KiB)

1676658475290.png (128.8 KiB)

Answer 1 · 2023-02-17T23:19:16Z

Jason Lightfoot answered Feb 17 2023 at 11:19 PM Jason Lightfoot commented Feb 27 2023 at 1:58 PM

It is possible to customize the table view to show the numeric value as a date time string - but you should ask the developers to add that as a standard formatting option for a table in the Ideas section.

In your select statement you can use for each field respectively the DateTime constructor to get the string value:

DateTime(InputTimestamp).toString() AS InputTimestamp

You can change how the string is formatted using a format string.

Note that once it's a string in FlexSIm and manipulates are harder so it's easier to work with the numeric value. For this reason I'd import the numeric value AND a formatted string into two columns for now:

SELECT InputTimestamp, DateTime(InputTimestamp).toString() AS InputDatetimeStr  FROM....

· 10

kauan pedroso commented · Feb 20 2023 at 4:26 PM

So @Jason Lightfoot did what you had written but i ended up getting this error.

0 ·

1676910322076.png (2.7 KiB)

Jason Lightfoot ♦♦ kauan pedroso commented · Feb 20 2023 at 4:55 PM

Yes you have to use a function within FlexSim - so just import an empty string for InputDatetimeStr from SQLserver, and then run an update script (Table query with UPDATE sql) to set the empty string to the converted datetime using the InputDatetime field.

0 ·

kauan pedroso commented · Feb 20 2023 at 8:17 PM

I'm trying to use the same function I use in sql serve, but I'm not able to add the column.

0 ·

Jason Lightfoot ♦♦ kauan pedroso commented · Feb 20 2023 at 8:53 PM

FlexQL is closest to MySQL - it doesn't support that syntax.

You can just use:

SELECT InputTimestamp, '' AS InputTimestampStr FROM .......

when bringing in the data from SQL Server.

0 ·

kauan pedroso Jason Lightfoot ♦♦ commented · Feb 23 2023 at 11:54 AM

Hello @Jason Lightfoot , I managed to create the empty columns as you said, if you can help me how I create the code to run an update script to define an empty string for the converted datetime using the inputDatetime field, thank you.

0 ·

1677153246646.png (14.9 KiB)

Show more comments

question