CalCulated Column of DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CalCulated Column of DateTime

Author

Topic

brubond35@gmail.com
Starting Member

2 Posts

Posted - 2011-01-10 : 08:43:17

Hi,
I have a table with some columns in those columns 4 of them are date time columns, now I want to have a calculated column of date time data type in my table,
my requirement is the calculated column should hold the maximum among these 4 date columns,these columns can contain nulls also,
IS it possible to define calculated column for this requirement,
If not why?

Thanks
RaviKiran.M

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-10 : 09:35:50

You can, but SQL Server has no max across columns functions, so you'll have to write your own. The function will take each column as an input and then return the largest one. For null values I'd set them = to '17530101', i/e, @col1 = isnull(col1,'17530101'). This new column might just kill performance, so beware.

Jim

Everyday I learn something that somebody else already knew

brubond35@gmail.com
Starting Member

2 Posts

Posted - 2011-01-19 : 04:16:40

Hi,

Like jimf said I have realized that I have to write my own function,
can anybody give me some sample example for calculated column which is using user define function in its expression.

and

one more doubt is so I have to create a function, which req all the values of columns as parameters.so for my case I need to create a function with 4 parameters Right?

Thanks,
RaviKiran.M

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-19 : 07:13:03

[code]ALTER TABLE dbo.myTable ADD NewColumnName AS dbo.MyDateFunction(date1, date2, date3, date4)[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-19 : 07:16:56

[code]ALTER FUNCTION dbo.MyDateFunction (
@date1 datetime,
@date2 datetime,
@date3 datetime,
@date4 datetime
)
RETURNS DATETIME
AS
BEGIN

...

END[/code]Beware though that using a udf in a computed column definition will make it impossible to set as persisted, which again will blow your performance completely if you're doing filtering or joins or something based on it.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources