Getting Maximum Datetime Value Programmatically

How can I retrieve the maximum value of the DATETIME data type in SQL Server 2005 programmatically? I don't want to use NULL开发者_如何学JAVA as the column value, and I don't want to use CONVERT(DATETIME, '12/31/9999 23:59:59.997') either.

Thank you very much.

You can test the value like this:

PRINT 'The maximum date value '
    + CASE ISDATE('9999-12-31 23:59:59.998') + ISDATE('9999-12-31 23:59:59.999')
        WHEN 0 THEN 'IS LESS THAN'
        WHEN 1 THEN 'EQUALS'
        WHEN 2 THEN 'IS GREATER THAN'
        END
    + ' 9999-12-31 23:59:59.998'

Output:

The maximum date value EQUALS 9999-12-31 23:59:59.998

Should be in the docs... Up to December 31, 9999.

Because the lower-bounds and upper-bounds of date, datetime, datetime2(n), smalldatetime, etc are tedious to write-out, yet mostly have gotchas, I decided to use inline scalar UDFs to represent the min/max values - so I can either use these UDFs in queries directly, or just run SELECT dbo.GetMaxDateTime2(7); in a separate query window and copy-and-paste the literal value over when I can't use a UDF (like in a CHECK or DEFAULT constraint expression).

...it also includes my own editorialising in the comments :)

/* The `datetime2(n)` range is the same as `datetimeoffset(n)`'s */
CREATE FUNCTION dbo.GetMinDateTime2Value()
RETURNS datetime2(7)
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( datetime2(7), '0001-01-01 00:00:00.0000000' );
END;

GO

/* The `datetime2(n)` range is the same as `datetimeoffset(n)`'s */
CREATE FUNCTION dbo.GetMaxDateTime2Value( @n tinyint )
RETURNS datetime2(7)
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN
        CASE @n
            WHEN 0 THEN CONVERT( datetime2(0), '9999-12-31 23:59:59'         ) /* 6 bytes */
            WHEN 1 THEN CONVERT( datetime2(1), '9999-12-31 23:59:59.9'       ) /* 6 bytes */
            WHEN 2 THEN CONVERT( datetime2(2), '9999-12-31 23:59:59.99'      ) /* 6 bytes */
            WHEN 3 THEN CONVERT( datetime2(3), '9999-12-31 23:59:59.999'     ) /* 7 bytes */
            WHEN 4 THEN CONVERT( datetime2(4), '9999-12-31 23:59:59.9999'    ) /* 7 bytes */
            WHEN 5 THEN CONVERT( datetime2(5), '9999-12-31 23:59:59.99999'   ) /* 8 bytes */
            WHEN 6 THEN CONVERT( datetime2(6), '9999-12-31 23:59:59.999999'  ) /* 8 bytes */
            WHEN 7 THEN CONVERT( datetime2(7), '9999-12-31 23:59:59.9999999' ) /* 8 bytes */
        END;
END;

GO

CREATE FUNCTION dbo.GetMinDateTimeValue()
RETURNS datetime
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( datetime, '1753-01-01 00:00:00.000' ); /* The explanation of significance of the year 1751 can be found here: https://stackoverflow.com/q/3310569/159145 */
END;

GO

CREATE FUNCTION dbo.GetMaxDateTimeValue()
RETURNS datetime
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( datetime, '9999-12-31 23:59:59.997' ); /* 8 bytes - and yet is inferior to datetime2(7) in every way... how much alcohol was left in the room when the meeting to design the datetime type concluded? */
END;

GO

CREATE FUNCTION dbo.GetMinDateValue()
RETURNS date
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( date, '0001-01-01' ); /* I don't know why MS's docs mention Informatica's lower-bound as 1582-10-15 when it isn't even a Microsoft product. */
END;

GO

CREATE FUNCTION dbo.GetMaxDateValue()
RETURNS date
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( date, '9999-12-31' );
END;

GO

CREATE FUNCTION dbo.GetMinSmallDateTimeValue()
RETURNS smalldatetime
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( smalldatetime, '1901-01-01 00:00' );
END;

GO

CREATE FUNCTION dbo.GetMaxSmallDateTimeValue()
RETURNS smalldatetime
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( smalldatetime, '2079-06-06 23:59' ); /* Technically `'2079-06-06 23:59:29.998'` is valid for input, but it will be rounded-down to 23:59:00. Values of `'2079-06-06 23:59:30'` or higher will cause a conversion error. */
END;

GO

CREATE FUNCTION dbadmin.GetMinTimeValue()
RETURNS time(7)
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN CONVERT( time, '00:00:00.0000000' ); /* implicitly convertible from `time(7)` to `time(0)`. */
END;

GO

CREATE FUNCTION dbadmin.GetMaxTimeValue( @n tinyint )
RETURNS time(7)
    WITH SCHEMABINDING, INLINE = ON
AS
BEGIN
    RETURN
        CASE @n
            WHEN 0 THEN CONVERT( time(0), '23:59:59'         ) /* 3 bytes */
            WHEN 1 THEN CONVERT( time(1), '23:59:59.9'       ) /* 3 bytes */
            WHEN 2 THEN CONVERT( time(2), '23:59:59.99'      ) /* 3 bytes */
            WHEN 3 THEN CONVERT( time(3), '23:59:59.999'     ) /* 4 bytes */
            WHEN 4 THEN CONVERT( time(4), '23:59:59.9999'    ) /* 4 bytes */
            WHEN 5 THEN CONVERT( time(5), '23:59:59.99999'   ) /* 5 bytes */
            WHEN 6 THEN CONVERT( time(6), '23:59:59.999999'  ) /* 5 bytes */
            WHEN 7 THEN CONVERT( time(7), '23:59:59.9999999' ) /* 5 bytes */
        END;
END;

GO

And here's a table showing the results of those functions for all date+time types in SQL Server:

The above table comes from the below query (but transposed to better fit in StackOverflow's uncomfortably narrow post content area). (The CONVERT func is needed as some of those functions return datetime2(7) or time(7) for all @n parameters, so you'd see more fractional-seconds digits than you would with the smaller type).

SELECT
    'Minimum' AS "Value",

    CONVERT( time(0), dbo.GetMinTimeValue() ) AS "time(0)",
    CONVERT( time(1), dbo.GetMinTimeValue() ) AS "time(1)",
    CONVERT( time(2), dbo.GetMinTimeValue() ) AS "time(2)",
    CONVERT( time(3), dbo.GetMinTimeValue() ) AS "time(3)",
    CONVERT( time(4), dbo.GetMinTimeValue() ) AS "time(4)",
    CONVERT( time(5), dbo.GetMinTimeValue() ) AS "time(5)",
    CONVERT( time(6), dbo.GetMinTimeValue() ) AS "time(6)",
    CONVERT( time(7), dbo.GetMinTimeValue() ) AS "time(7)",

    dbo.GetMinDateValue()          AS "date",
    dbo.GetMinDateTimeValue()      AS "datetime",
    dbo.GetMinSmallDateTimeValue() AS "smalldatetime",

    CONVERT( datetime2(0), dbo.GetMinDateTime2Value() ) AS "datetime2(0)",
    CONVERT( datetime2(1), dbo.GetMinDateTime2Value() ) AS "datetime2(1)",
    CONVERT( datetime2(2), dbo.GetMinDateTime2Value() ) AS "datetime2(2)",
    CONVERT( datetime2(3), dbo.GetMinDateTime2Value() ) AS "datetime2(3)",
    CONVERT( datetime2(4), dbo.GetMinDateTime2Value() ) AS "datetime2(4)",
    CONVERT( datetime2(5), dbo.GetMinDateTime2Value() ) AS "datetime2(5)",
    CONVERT( datetime2(6), dbo.GetMinDateTime2Value() ) AS "datetime2(6)",
    CONVERT( datetime2(7), dbo.GetMinDateTime2Value() ) AS "datetime2(7)"

UNION

SELECT
    'Maximum' AS "Value",

    CONVERT( time(0), dbo.GetMaxTimeValue(0) ) AS "time(0)",
    CONVERT( time(1), dbo.GetMaxTimeValue(1) ) AS "time(1)",
    CONVERT( time(2), dbo.GetMaxTimeValue(2) ) AS "time(2)",
    CONVERT( time(3), dbo.GetMaxTimeValue(3) ) AS "time(3)",
    CONVERT( time(4), dbo.GetMaxTimeValue(4) ) AS "time(4)",
    CONVERT( time(5), dbo.GetMaxTimeValue(5) ) AS "time(5)",
    CONVERT( time(6), dbo.GetMaxTimeValue(6) ) AS "time(6)",
    CONVERT( time(7), dbo.GetMaxTimeValue(7) ) AS "time(7)",

    dbo.GetMaxDateValue()          AS "date",
    dbo.GetMaxDateTimeValue()      AS "datetime",
    dbo.GetMaxSmallDateTimeValue() AS "smalldatetime",

    CONVERT( datetime2(0), dbo.GetMaxDateTime2Value(0) ) AS "datetime2(0)",
    CONVERT( datetime2(1), dbo.GetMaxDateTime2Value(1) ) AS "datetime2(1)",
    CONVERT( datetime2(2), dbo.GetMaxDateTime2Value(2) ) AS "datetime2(2)",
    CONVERT( datetime2(3), dbo.GetMaxDateTime2Value(3) ) AS "datetime2(3)",
    CONVERT( datetime2(4), dbo.GetMaxDateTime2Value(4) ) AS "datetime2(4)",
    CONVERT( datetime2(5), dbo.GetMaxDateTime2Value(5) ) AS "datetime2(5)",
    CONVERT( datetime2(6), dbo.GetMaxDateTime2Value(6) ) AS "datetime2(6)",
    CONVERT( datetime2(7), dbo.GetMaxDateTime2Value(7) ) AS "datetime2(7)";

Don't forget that T-SQL allows implicit conversions to narrower types when it causes data-loss, it doesn't even give you a warning message (e.g. you can do DECLARE @maxdt1 datetime2(1) = dbadmin.GetMaxDateTime2Value(7); SELECT @maxdt1;, *grumble*)