How to make conversions from varchar to datetime deterministic?

Question

In the tradition of this question and in light of the documentation, how does one make this function deterministic:

ALTER FUNCTION [udf_DateTimeFromDataDtID]
(
    @DATA_DT_ID int -- In form YYYYMMDD
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    RETURN CONVERT(datetime, CONVERT(varchar, @DATA_DT_ID))
END

Or this one (because of the string/date literals - and yes, I've also tried '1900-01-01'):

ALTER FUNCTION udf_CappedDate
(
    @DateTimeIn datetime
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    IF @DateTimeIn < '1/1/1900'
     RETURN '1/1/1900'
    ELSE IF @DateTimeIn > '1/1/2100'
     RETURN '1/1/2100'

    RETURN @DateTimeIn
END

Answer 1

From the articles you linked:

To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

You need to use a style parameter in your conversions to datetime.

For example:

CONVERT(datetime, '2008-01-01', 121)

Except don't use 121...

Answer 2

BOL says that CONVERT is deterministic with datetimes if the style parameter is specified. So if you change the first UDF to:

RETURN CONVERT(datetime, CONVERT(varchar, @DATA_DT_ID), 112)

Then it should be deterministic, if I understand the docs correctly.

Presumably, the same trick could be used in your second UDF:

IF @DateTimeIn < CONVERT(datetime, '1/1/1900', 101)
    RETURN CONVERT(datetime, '1/1/1900', 101)

I really wish there were a way to specify datetime literals in T-SQL.

EDIT:

As pointed out by Arvo in the comments (thank you, Arvo), the ODBC timestamp literal format can be used (even when using OLE DB) so the second function above could be better written as:

IF @DateTimeIn < {d '1900-01-01'}
    RETURN {d '1900-01-01'}
...etc.

and the conversion to datetime is done at compile time instead of execution time. Note that the format of the date has to be very specific (see Arvo's link to the datetime data type):

 d    yyyy-mm-dd
 t    hh:mm:ss[.fff]
ts    yyyy-mm-dd hh:mm:ss[.fff]