How to find the maximum possible value of a given datatype

[jshunter]

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

[Roy Harvey]

>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

[Ed Murphy]

js******@waikat o.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.

[Gert-Jan Strik]

No there isn't.

But there are solutions. You can read more at
http://groups.google.nl/group/micros...43f4aea485c6d1
(url may wrap)

HTH,
Gert-Jan
"js******@waika to.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