How to find the maximum possible value of a given datatype

Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:

select datalength(cast(getdate() as smalldatetime))

....but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'

I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution

--John Hunter

Re: How to find the maximum possible value of a given datatype

How about just hardcoding what is in the documentation? DATETIME, for
example says "Date and time data from January 1, 1753 through December
31, 9999, to an accuracy of one three-hundredth of a second
(equivalent to 3.33 milliseconds or 0.00333 seconds)." Which after
consulting the accompanying chart comes to 9999-12-31 23:59:59.997.

Roy Harvey
Beacon Falls, CT

On Wed, 27 Jun 2007 01:39:49 -0000, "jshunter@waikato.ac.nz"
wrote:

>Does anyone know of a built-in function to return the maximum possible
>value of a given datatype? I have to return the biggest value for a
>smalldatetime or datetime in a view if the field is null, but can't
>find such a function. The closest I've come is:
>
>select datalength(cast(getdate() as smalldatetime))
>
>...but that only return the number of bytes, not the value itself,
>which is '6-6-2079 11:59'
>
>I know I could create my own lookup table and function, but I was
>hoping that Transact-SQL would have a built-in solution
>
>--John Hunter

Re: How to find the maximum possible value of a given datatype

On Jun 27, 1:56 pm, Roy Harvey wrote:
> How about just hardcoding what is in the documentation? DATETIME, for
> example says "Date and time data from January 1, 1753 through December
> 31, 9999, to an accuracy of one three-hundredth of a second
> (equivalent to 3.33 milliseconds or 0.00333 seconds)." Which after
> consulting the accompanying chart comes to 9999-12-31 23:59:59.997.
>
> Roy Harvey
> Beacon Falls, CT

I've had to do just that, but it would be nice if I could have the
view do the job via a function. For example, if the underlying table
changed from a smalldatetime to a datetime, then all views which use
it would automatically return the correct maximum value

--John Hunter

Re: How to find the maximum possible value of a given datatype

On Wed, 27 Jun 2007 02:04:39 -0000, "jshunter@waikato.ac.nz"
wrote:

>On Jun 27, 1:56 pm, Roy Harvey wrote:
>> How about just hardcoding what is in the documentation? DATETIME, for
>> example says "Date and time data from January 1, 1753 through December
>> 31, 9999, to an accuracy of one three-hundredth of a second
>> (equivalent to 3.33 milliseconds or 0.00333 seconds)." Which after
>> consulting the accompanying chart comes to 9999-12-31 23:59:59.997.
>>
>> Roy Harvey
>> Beacon Falls, CT
>
>I've had to do just that, but it would be nice if I could have the
>view do the job via a function. For example, if the underlying table
>changed from a smalldatetime to a datetime, then all views which use
>it would automatically return the correct maximum value
>
>--John Hunter

One alternative is to write your own function that encapsulates the
hardcode. Pass the table and column names, query the system tables
for the type and related information such as max length of a varchar,
and then return it. Clumsy, no doubt.

Or reconsider the design decision of using maximum values in place of
NULLs.

Roy Harvey
Beacon Falls, CT

Re: How to find the maximum possible value of a given datatype

>One alternative is to write your own function that encapsulates the
>hardcode. Pass the table and column names, query the system tables
>for the type and related information such as max length of a varchar,
>and then return it. Clumsy, no doubt.

My apologies, it is nowhere near as simple as I described as, at a
minimum, one function for each datatype group (dates, character
strings, etc) would be required.

Roy Harvey
Beacon Falls, CT

Re: How to find the maximum possible value of a given datatype

jshunter@waikato.ac.nz wrote:

> Does anyone know of a built-in function to return the maximum possible
> value of a given datatype? I have to return the biggest value for a
> smalldatetime or datetime in a view if the field is null,

If you (and the recipient of this return value, if it isn't another
piece of T-SQL) can wrap your head around ternary logic, then you
can use the null value as is:

where not (some_date > end_date)

Or, depending on the expected longevity of the system, you can (a)
hard-code June 6, 2079 (max smalldatetime) or (b) avoid smalldatetime
and hard-code December 31, 9999 (max datetime). I would personally
go with (b), as I can't imagine actually designing a system that had
a good reason to use smalldatetime for anything.

Re: How to find the maximum possible value of a given datatype

No there isn't.

But there are solutions. You can read more at
http://groups.google.nl/group/microsoft.public.sqlserver.pro gramming/browse_thread/thread/46512c2691da4607/6743f4aea485c 6d1
(url may wrap)

HTH,
Gert-Jan


"jshunter@waikato.ac.nz" wrote:
>
> Does anyone know of a built-in function to return the maximum possible
> value of a given datatype? I have to return the biggest value for a
> smalldatetime or datetime in a view if the field is null, but can't
> find such a function. The closest I've come is:
>
> select datalength(cast(getdate() as smalldatetime))
>
> ...but that only return the number of bytes, not the value itself,
> which is '6-6-2079 11:59'
>
> I know I could create my own lookup table and function, but I was
> hoping that Transact-SQL would have a built-in solution
>
> --John Hunter