Is there a 'max' function in T-SQL?

[mkrausnick]

Is there a one-statement way to code this T-SQL:

	IF @dBeginDate > '11-01-2007 00:00'
	   SET @PeriodStart = @dBeginDate
	ELSE 
	   SET @PeriodStart = '11-01-2007 00:00'

In VFP I would write

dPeriodStart=iif(dBeginDate > {^2007-11-01}, dBeginDate , {^2007-11-01})

or

dPeriodStart = max(dBeginDate , {^2007-11-01})

IF/ELSE seems clunky to me for such a simple statement. Is there another way?

Mike Krausnick
Dublin, California

 

[gmmastros]

There's a variety of ways that you could write the same thing, but.... in my opinion, what you have written is the best way.

For example, you could write a user defined function that accepts 2 datetime's as parameters and have it return the max. It's pretty simple and would only take a minute or two to write. In my opinion, it's better to NOT write a function like that because using too many functions can slow down your performance.

One way....

Set @PeriodStart = Case When @dBeginDate > '20071101' 
                        Then @dBeginDate 
                        Else '20071101' End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should also mention that there IS a max function, but it doesn't do what you want it to do (at least not directly). Max is an aggregate function that accepts a single column as it's parameter. It returns the maximum value found within that column (ignoring null values).


Ex:

Select Max(Column1) As MaxColumn1 From MyFavoriteTable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

actually, there ~is~ a function that does exactly what was being requested here -- it's called GREATEST(), and there's also a LEAST() function

these are standard SQL functions, but, for some reason, SQL Server doesn't support them



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

That's interesting, Rudy, but not too helpful in a Microsoft SQL Server forum, right?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

disappointing, maybe

but knowledge of standard SQL is hardly ever unhelpful

why, you never know, the next time something pops up that standard SQL covers, maybe Microsoft SQL Server ~will~ support it

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mkrausnick]

Thanks to you both. I figured as much, but not being a T-SQL expert, I thought I'd ask anyway.

Mike Krausnick
Dublin, California