FORMATDATETIME produces incorrect hour

[Savannah Durbin]

Seeing an issue where the output of FORMATDATETIME appears to be incorrect for a particular value:

SET @date = '2019-03-10 02:00:00';
SELECT FORMATDATETIME(@date, 'yyyy-MM-dd HH:mm:ss'), YEAR(@date), MONTH(@date), DAY(@date), HOUR(@date);

FORMATDATETIME(@DATE, 'yyyy-MM-dd HH:mm:ss')	YEAR(@DATE)	MONTH(@DATE)	DAY(@DATE)	HOUR(@DATE)
2019-03-10 03:00:00	2019	3	10	2

The hour is being output as "03" instead of the expected value of "02".

I'm running FORMATDATETIME for every hour of the year 2019, and only see this issue happening for one specific date in 2019 as far as I can tell: 2019-03-10 02:00:00.

I also tried for the year 2020, and a different date produces the incorrect output:

SET @date = '2020-03-08 02:00:00';
SELECT FORMATDATETIME(@date, 'yyyy-MM-dd HH:mm:ss'), YEAR(@date), MONTH(@date), DAY(@date), HOUR(@date);

FORMATDATETIME(@DATE, 'yyyy-MM-dd HH:mm:ss')	YEAR(@DATE)	MONTH(@DATE)	DAY(@DATE)	HOUR(@DATE)
2020-03-08 03:00:00	2020	3	8	2

I am running version 1.4.197 (also tried on 1.4.199).

[Evgenij Ryazanov]

Hello.

It looks like your timezone has a DST transition from 2019-03-10 02:00:00 to 2019-03-10 03:00:00, so timestamp 2019-03-10 02:00:00 is not valid. This function always normalizes such timestamps.

You can use the TIMESTAMP WITH TIME ZONE data type to avoid issues on DST boundaries.

SET @date = TIMESTAMP WITH TIME ZONE '2020-03-08 02:00:00 -07';

[Savannah Durbin]

Ah, that makes sense. Thanks!