Newbie - UDF column level question

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

General SQL Server Forums

New to SQL Server Programming

Newbie - UDF column level question

Author

Topic

sugarcrum
Starting Member

3 Posts

Posted - 2009-01-20 : 18:12:45

Long time Oracle user moving to SQL Server. From what I'm reading this should be an easy question, but, I want to make sure my understanding is correct.

I have a table: CUSTOMER (and several columns), then have EXPIRATION_DATE and EFFECTIVE_DATE.

Now, what I'd like to do is add a column, say, named ISACTIVE. This column should be nothing but a simple check of the EXPIRATION_DATE and EFFECTIVE_DATE columns to make sure that a date (not necessarily the current date, but, a date passed in) is within these two columns, and, if so return true (or 1 ... whatever).

Make sense?

So,
CUSTOMER.EFFECTIVE_DATE = 01/01/2008
CUSTOMER.EXPIRATION_DATE = 01/01/2010

I want to do a "Select * from CUSTOMER where IsActive('01/01/2009') = 1" .. (or something similar .. you get the gist).

Can this be easily done?

-- sugarcrum

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-20 : 18:54:04

I guess, Im not sure if my answer makes any sense but When you define the column itself, you can say that is of the "datetime" data type and that would just pass the validation.

regards,
Anil Kumar.

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-20 : 22:34:14

Does it have to be in a function? Why not just do in a select?
For example:


declare @DateInput datetime
set @DateInput = '01/01/2009'

select *
from   dbo.Customer
where  Effective_Date < @DateInput and
       Expiration_Date > @DateInput

Nathan Skerl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:53:17

or did you meant this?


select *,
case when Effective_Date < @DateInput and
       Expiration_Date > @DateInput then 1
     else 0
end as IsActive
from   dbo.Customer

sugarcrum
Starting Member

3 Posts

Posted - 2009-01-27 : 11:02:04

Thanks for the replies. The SQL part makes sense and is not the challenge that I'm having. Again, new to SQL server so my question just may not make sense.

Can I have a UDF that could have this code which IsActive field uses? Reason for this is because this will be used in a reporting structure for users. They will be able to pull over the "IsActive" field and it should just "know" how to handle this date. We will not be controling the sql, make sense? So, we'd like to put this as a UDF.

-- sugarcrum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:02:54

yup. you can. but if this is for a report why not embed this logic in report itself, so that you wont have to touch sql at all. If sql reports, you can do it either inside expression box of textbox or inside custom code part.

sugarcrum
Starting Member

3 Posts

Posted - 2009-01-27 : 12:15:07

I'll look into that. Thanks visakh

-- sugarcrum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:20:30

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources