0

I have a table which nonsensically has DateTime values stored in Year (INT), Month (INT), Day (INT) and Time (FLOAT) columns; unfortunately I'm not allowed to change this but have a requirement to partition the table by Year and Month. Therefore, I am trying to create a persisted computed-column to hold the DateTime in a DATETIME2 format. However, my CreateDateTime scalar function is being deemed as "non-deterministic" when I try to add a computed-column based upon it.

This is my CreateDateTime function.

CREATE FUNCTION dbo.CreateDateTime
(
    @year SMALLINT,
    @month TINYINT,
    @day TINYINT,
    @time FLOAT
)
RETURNS DATETIME2
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @paddedYear VARCHAR(4) = FORMAT(@year, '0000')
    DECLARE @paddedMonth VARCHAR(2) = FORMAT(@month, '00')
    DECLARE @paddedDay VARCHAR(2) = FORMAT(@day, '00')
    DECLARE @formattedTime VARCHAR(8) = FORMAT(@time, '00:00:00')
    RETURN IIF
    (
        @year IS NULL
        OR @month IS NULL
        OR @day IS NULL,
        NULL,
        CAST
        (
            IIF
            (
                @time IS NULL OR TRY_CONVERT(TIME, @formattedTime) IS NULL,
                @paddedYear
                + @paddedMonth
                + @paddedDay,
                @paddedYear
                + @paddedMonth
                + @paddedDay
                + ' '
                + @formattedTime
            )
            AS DATETIME2
        )
    )
END

But when used in order to attempt to add the computed-column for DateTime (with the following script), it results in the following error message.

ALTER TABLE dbo.Logs
ADD [DateTime] AS (dbo.CreateDateTime(RY, RM, RD, RT)) PERSISTED

Computed column 'DateTime' in table 'Logs' cannot be persisted because the column is non-deterministic.

I've tried altering CreateDateTime down to a very basic function which returns a fixed DateTime using the following: CAST('20000101' AS DATETIME2), CONVERT(DATETIME2, '20000101') and '20000101' but all produce the same error as above. I managed to get the column added though if I made the CreateDateTime function return a VARCHAR(8) instead so it looks like a problem with DATETIME2.

How can I create this CreateDateTime function so it is deemed as deterministic and still returns a DATETIME2?

Matt Arnold
  • 668
  • 2
  • 8
  • 21
  • 1
    If you only need year and month, why use `DateTime2` and not `Date`? If you can use `Date`, try `ALTER TABLE dbo.Logs add createDate as datefromparts(Ry, Rm, Rd) persisted` – Zohar Peled Apr 16 '19 at 10:01
  • I would be using the entire DateTime for comparison with other records on other tables for other purposes. I guess I could add a non-persisted column for this and then try a persisted one for `Date` but is there really no way to do this with a `DateTime2`? – Matt Arnold Apr 16 '19 at 10:05
  • I've tried changing the `CreateDateTime` function to just return a `Date` now anyway and it results in the same error when all it does is this: `RETURN CAST('20000101' AS DATE)`. – Matt Arnold Apr 16 '19 at 10:08
  • I don't think the problem is with the data type. I think it's with the fact that you are using a udf. How is time stored in your table? – Zohar Peled Apr 16 '19 at 10:11
  • RT (Time) is a `FLOAT` in the Logs table. I've managed to get the persisted computed-column to generate using the UDF if it returns a `VARCHAR(8)`, that's why I figured it's a problem relating to the DateTime types. – Matt Arnold Apr 16 '19 at 10:14
  • Yes, you wrote it's a float, but what does that float represent? The number of hours since midnight? the number of minutes since 7 A.M? – Zohar Peled Apr 16 '19 at 10:18
  • The `FLOAT` is holding time in the format `HH:mm:ss.mmm`. That's to say if the time is "12:00:01.000 AM", all that will be in RT is: 1, "12:01:01.000 AM" would become: 101, and the largest valid value in the field would be: 235959.999. – Matt Arnold Apr 16 '19 at 10:34

1 Answers1

2

I was able to create the persisted computed-column by bypassing my CreateDateTime function and instead use the following:

ALTER TABLE dbo.Logs
ADD [DateTime] AS DATETIME2FROMPARTS
(
    RY,
    RM,
    RD,
    ROUND(RT / 10000, 0, 1),
    ROUND(RT / 100, 0, 1) - ROUND(RT / 10000, 0, 1) * 100,
    ROUND(RT, 0, 1) - ROUND(RT / 100, 0, 1) * 100,
    RT - ROUND(RT, 0, 1),
    3 --Precision to 3 decimal places (for milliseconds)
) PERSISTED

Whilst this doesn't answer my question about creating a deterministic function returning a DATETIME2; it does solve the specific problem I was having of creating a DateTime2 persisted computed-column to show the DateTime for each RY, RM, RD and RT value.

As highlighted by Zohar, DATETIMEFROMPARTS does not return a DATETIME2; DATETIME2FROMPARTS also takes an addition argument for decimal precision - this nullifies the rounding errors which were present in the original DATETIMEFROMPARTS solution I had provided.

Matt Arnold
  • 668
  • 2
  • 8
  • 21