Add 64 bit version of parseDateTime (parseDateTime64)

[mlazowik]

In #46815 parseDateTime was added, but there's on 64 bit version of that function. It would be useful for the same reasons as parseDateTime, but with extended supported dates range.

Describe alternatives you've considered

Reshaping the known format in sql so that toDateTime64 can be used.

[wekt0r]

For more context: parseDateTime64BestEffort is not a great way to deal with arbitrary strings with known format, e.g.:

select parseDateTimeOrNull('01/13/2024', '%d/%m/%Y') -- null, as expected
select parseDateTime64BestEffortOrNull('01/12/2024') -- 2024-12-01, as expected
select parseDateTime64BestEffortOrNull('01/13/2024') -- 2024-01-13, welp

And toDateTime64 seems to be a bad choice, since it behaves more like a cast, not an actual parser. For example:

select toDateTime64('2024-13-01', 3) -- 1900-01-01 ?
select toDateTime64('2024-ab-cd ::::::::', 3) -- 1900-01-05 15:51:50.000 ?

[den-crane]

There is parseDateTime64BestEffort and parseDateTime64BestEffortUS

SELECT parseDateTime64BestEffortUSOrNull('01/13/2024')

   ┌─parseDateTime64BestEffortUSOrNull('01/13/2024')─┐
1. │                         2024-01-13 00:00:00.000 │
   └─────────────────────────────────────────────────┘

[wekt0r]

Hi @den-crane, thanks for the response! I'm not sure we're on the same page, so let me clarify - in cases like '01/13/2024', functions parseDateTime64BestEffortOrNull('01/13/2024') and parseDateTime64BestEffortUSOrNull('01/13/2024') behave the same, as the US only modifies the order of the formats, and uses non-US version as a fallback. We actually want the parser with US format to never execute, and return null on the us-like dates (or ideally - return null on any formats other than '%d/%m/%Y') .

[den-crane]

So you need f('01/13/2024') -> null (DateTime64) because it's the incorrect date, because of your internal requirements.