Casting in OQL

1
Greetings I’ve scoured the documentation but details are distressingly sparse. How do I do a cast from string to datetime? What format does the string need to be in? Here follows a list of things I’ve tried that gave me a “Invalid datetime format” error. cast(‘2018/12/01’ as datetime) cast(‘2018-12-01’ as datetime) cast(‘01/12/2018’ as datetime) cast(‘01/12/2018 00:00:00’ as datetime) cast(‘2018/12/01 00:00:00’ as datetime) cast(‘01/12/2018 00:00:00:0000’ as datetime) cast(‘2018/12/01 00:00:00:0000’ as datetime) CAST('31/DEC/2012 08:00:00 PM' AS DATETIME) CAST('1 DECEMBER 2018' AS DATETIME) CAST('1543622400' AS DATETIME) (timestamp) I give up. The only conclusion I can reach is that the cast functionality cannot actually cast anything to datetime.
asked
1 answers
3

The cast function in OQL does function but not when using the build in database.

In an example I retrieve all records in a dataset from the MyFirstModule.Entity entity where the test (datetime) field has a value larger than 4/23/2018 1:37 PM. See below.

SELECT A.test as TEST
FROM MyFirstModule.Entity as A
WHERE A.test > CAST('4/23/2018 1:37 PM' AS DATETIME)

When using this function in conjunction with a hsql db this will fail with the error you mentioned, switching to postgres solves the issue. As no mention is made in the documentation that the function is restricted to certain db-type this should be considered as a bug IMHO. But if using the postgres db is an option for you then you now have a work around.

answered