Implementing a rotating partition scheme, per [kejser.org/table-pattern-rotating-log-ring-](https://web.archive.org/web/20220630220433/kejser.org/table-pattern-rotating-log-ring-buffer/) . Ran into an issue with DATEDIFF rounding up values:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, dtTrunc
, dtDiff
, PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
(
VALUES
('2024-08-17 23:59:59.997')
, ('2024-08-17 23:59:59.998')
, ('2024-08-17 23:59:59.999')
, ('2024-08-18 00:00:00.000')
)
AS v(dt)
CROSS APPLY
(
SELECT
dt = CAST(v.dt AS DATETIME2(3))
) a1
CROSS APPLY
(
SELECT
dtTrunc = CAST(a1.dt AS DATE)
, dtDiff = DATEDIFF(day, 0, a1.dt)
) a2
Problem solved with cast to date:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, dtTrunc
, dtDiff
, PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
(
VALUES
('2024-08-17 23:59:59.997')
, ('2024-08-17 23:59:59.998')
, ('2024-08-17 23:59:59.999')
, ('2024-08-18 00:00:00.000')
)
AS v(dt)
CROSS APPLY
(
SELECT
dt = CAST(v.dt AS DATETIME2(3))
) a1
CROSS APPLY
(
SELECT
dtTrunc = CAST(a1.dt AS DATE)
, dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE))
) a2
Is this expected / documented behaviour? If so, where?
Problem solved with cast to date:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, dtTrunc
, dtDiff
, PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
(
VALUES
('2024-08-17 23:59:59.997')
, ('2024-08-17 23:59:59.998')
, ('2024-08-17 23:59:59.999')
, ('2024-08-18 00:00:00.000')
)
AS v(dt)
CROSS APPLY
(
SELECT
dt = CAST(v.dt AS DATETIME2(3))
) a1
CROSS APPLY
(
SELECT
dtTrunc = CAST(a1.dt AS DATE)
, dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE))
) a2
Is this expected / documented behaviour? If so, where?
Asked by Mark Storey-Smith
(31860 rep)
Aug 27, 2024, 02:21 PM
Last activity: Aug 29, 2024, 09:37 AM
Last activity: Aug 29, 2024, 09:37 AM