2015-01-09

Leap Second and Day

Leap Second

A positive leap second 2015-06-30 has been announced by International Earth Rotation & Reference Systems Service:
hpiers.obspm.fr/iers/bul/bulc/bulletinc.dat
SQL Server gets the time from Windows, who knows how to handle a leap second (KB909614).
The leap second is „smeared“ so that the timestamps are not affected.
This gives that the leap second can not be registered.

In T-SQL it will look like this
(statement)
DECLARE @before_leap_second DATETIME = '2015-06-30 23:59:59'
SELECT @before_leap_second AS [normal_time];

(result)
normal_time
-----------------------
2015-06-30 23:59:59.000


(statement)
DECLARE @leap_second DATETIME = '2015-06-30 23:59:60';
SELECT @leap_second AS [leap_second];

(result)
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

leap_second
-----------------------
NULL


Leap Day

A positive leap day is more precise defined. This gives that it can be defined in a data type, and calculations can be done correct. The Leap Day is nicely handled by T-SQL like this
(statement)
DECLARE @leap_day DATETIME = '2012-02-29';
SELECT @leap_day AS [leap_second];

(result)
leap_day
-----------------------
2012-02-29 00:00:00.000


Also a more tricky Leap Day is handled correct by T-SQL like this
(statement)
DECLARE @leap_day DATETIME = '2000-02-29';
SELECT @leap_day AS [leap_day];

(result)
leap_day
-----------------------
2000-02-29 00:00:00.000


An attempt to use a wrong Leap Day will give an error like above
(statement)
DECLARE @not_leap_day DATETIME = '2013-02-29'
SELECT @not_leap_day AS [not_leap_day];

(result)
Msg 242, Level 16, State 3, Line 11
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

not_leap_day
-----------------------
NULL


History

2015-01-09 Blog entry about Leap Second.
2015-02-10 Blog entry added section about Leap Year.