DateTime and Date Examples

[gramian]

Dear All,

I tested some date-related examples, below are some that did not work. Could you check if this is expected or a bug?

• SELECT '2022-10-22 10:11:12'.asDateTime() -- works

• SELECT date('2022-10-22 10:11:12.000','yyyy-MM-dd HH:mm:ss.SSS') -- works (but milliseconds are not returned)

• SELECT date('2022-10-22 10:11:12','yyyy-mm-dd') -- fails (Text '2022-10-22 10:11:12' could not be parsed, unparsed text found at index 10)

• SELECT '2022-10-22 10:11:12.001'.asDateTime() -- fails (Text '2022-10-22 10:11:12.001' could not be parsed, unparsed text found at index 19)

• SELECT '2022-10-22T10:11:12'.asDateTime() -- fails (Text '2022-10-22T10:11:12' could not be parsed at index 10)

• SELECT '2022-10-22 10:11:12'.asDate() -- fails (Text '2022-10-22 10:11:12' could not be parsed, unparsed text found at index 10)

• SELECT '2022-10-22'.asDate() -- fails (Text '2022-10-22' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO resolved to 2022-10-22 of type java.time.format.Parsed)

Thank you

[gramian]

Furthermore, here are some examples of duration and precision that may not work work correctly:

• SELECT duration('2022-10-22 15:23:45'.asDateTime()-'2022-01-22 10:17:11'.asDateTime(),'year') -- fails (amount 'PT6557H6M34S' not a number ot a string)

• SELECT duration(1234567,'second') -- works

• SELECT duration(1234567,'millisecond') -- works

• SELECT duration(1234567,'year') -- fails (Unit must not have an estimated duration)

• SELECT duration(date(12345678),'second') -- fails (amount 'Thu Jan 01 05:25:45 CET 1970' not a number ot a string)

• SELECT '2022-10-22 15:23:45'.asDateTime().precision('second') -- no error but only returns just the argument

• SELECT '2022-10-22 15:23:45'.asDateTime().precision('year') -- fails (Not supported conversion from 'Millis' to 'Years')

It seems I may misinterpret the types and their interaction behind this operations?

[vic0824]

If I replace SELECT date('2022-10-22 10:11:12','yyyy-mm-dd') with SELECT date('2022-10-22 10:11:12','yyyy-MM-dd HH:mm:ss') it works, so maybe there is a constraint that the value should have the same number of tokens as the format.

[lvca]

Correct, the format must be compatible with the date passed.