Convert float / double field type to date

0
Hi all, I need to import data from a SQL database into the Mendix database. Some attributes in the SQL database contain dates and the attributes are defined as (float, null). I would like to convert those fields to date fields in Mendix in order to be able to use date functions in the Mendix apps. How can I convert the “(float, null)” fields to date (or string) field types in Mendix?
asked
4 answers
0

There is a java action in the Community Commons module for converting Unix timestamps(ms) into DateTime. https://docs.mendix.com/appstore/modules/community-commons-function-library#4-2-datetime 

Depends on the format of your float but if it was the Unix timestamp in seconds then could times by 1000 to get milliseconds, convert to integer/long and pass into the LongToDateTime java action.

answered
0

 

Example values as shown in the SQL database all result in 1/1/1970 when using the epochToDateTime function: 41943.5662240625

44062.5469161458

37196

answered
0

Hi Robert,

When debugging with the following values in the SQL database:

41943.5662240625 and 42395.4232387731

I get the following values in the variables:

the epochToDateTime expects an Integer as input, however the values in the SQL database look like Decimals.

When I change the input parameter to a Decimal, then this epochToDateTime function generates an error in StudioPro.

answered
0

How are you exporting the data from your SQL database? 

If this is using Transact-SQL, you would be better running the value through CONVERT on the SQL Server.
e.g. 

SELECT CONVERT(datetime, 40486.6908449074)

 

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

answered