Is there a "Try" equivelent of DATETIME2FROMPARTS?

Question

Given the following fields:

Y INT, --Year.
M INT, --Month.
D INT, --Day.
T FLOAT --Hours (H), minutes (m), seconds (s) and milliseconds (x) (in the form HHmmss.xxx).

Is there a way to attempt to convert these values into a DATETIME2 without having to first convert them to a string-based data type? If the attempt fails (e.g. due to an overflowing day value (e.g. 35th of January 2019)), I would like NULL to be returned.

If I use DATETIME2FROMPARTS it will simply fail if passed any invalid component.

I am trying to avoid converting these into a string as the only reason I am trying this compromised solution is because the performance of my other solution which actually handles overflow and underflow, is incredibly slow against a large database (doesn't complete in 5 hours!) so I'm trying to try something simpler to see if it improves the performance of adding this as a computed persisted column.

Is it too late to change your schema and store your datetime in a single column using a datetime data type? A [similar question](https://stackoverflow.com/q/29020054/1048425) has been asked before without much luck. — GarethD, May 28 '19 at 12:04
AFAIK you can't *attempt* to create datetime2 from parts. You can generate a string and use `try_convert` or `try_cast` quite easily, though. (`yyyy-MM-ddTHH:mm:ss.xxx`) — Zohar Peled, May 28 '19 at 12:15
Unfortunately it is; we need to keep these columns and add new records by them as they correspond directly to the data coming from the hardware which generates these values and we need them for tracability (although I'm sure there could be a better way of retaining this information). I believe I have a solution now specific to my problem; I'll post it once I've tested it. — Matt Arnold, May 28 '19 at 12:16
Aside: Adding constraints or a trigger to preclude storing goofy values would be another approach. — HABO, May 28 '19 at 13:01

score 2 · Answer 1 · answered May 28 '19 at 12:52

Here is the bespoke solution I ended up writing for this. There is unfortunately repeated logic since I'm using this in a persisted computed column and thus cannot use variables or scalar functions (for parameters to be used as variables).

DECLARE @Y INT = 2000, @M INT = 2, @D INT = 29, @T FLOAT = 135559.999

SELECT IIF
    (
        --If the Year is out-of-bounds.
        @Y <= 0 OR @Y > 9999
        --Or the Month is out-of-bounds.
        OR @M <= 0 OR @M > 12
        --Or the Day is out-of-bounds (Accounts for leap years).
        OR @D <= 0 OR @D > DAY(EOMONTH(DATETIME2FROMPARTS(@Y, @M, 1, 0, 0, 0, 0, 3)))
        --Or the Time is less than 0
        OR @T < 0
        --Or the Hour is out-of-bounds.
        OR ROUND(@T / 10000, 0, 1) >= 24
        --Or the Minute is out-of-bounds.
        OR ROUND(@T / 100, 0, 1) - ROUND(@T / 10000, 0, 1) * 100 >= 60
        --Or the Second is out-of-bounds.
        OR ROUND(@T, 0, 1) - ROUND(@T / 100, 0, 1) * 100 >= 60,
        --NULL is returned
        NULL,
        --Otherwise, the Date Time components are parsable into a DATETIME2.
        DATETIME2FROMPARTS
        (
            --Year
            @Y,
            --Month
            @M,
            --Day
            @D,
            --Hour
            ROUND(@T / 10000, 0, 1),
            --Minute
            ROUND(@T / 100, 0, 1) - ROUND(@T / 10000, 0, 1) * 100,
            --Second
            ROUND(@T, 0, 1) - ROUND(@T / 100, 0, 1) * 100,
            --Millisecond (multiplied by 1000 to use the first 3 decimal places).
            (@T - ROUND(@T, 0, 1)) * 1000,
            --Precision (3 is specified since only 3 decimal places become part of the integer for the fraction parameter above).
            3
        )
    )

If you need millisecond precision different to 3 decimal places, where your millisecond precision is x, change 3 to x and * 1000 on the line above to * POWER(10, x).

Is there a "Try" equivelent of DATETIME2FROMPARTS?

1 Answers1