Can I parameterize this UDF using DATENAME ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Can I parameterize this UDF using DATENAME ?

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-09-08 : 11:28:42

Is there a way to pass in the @datepart parameter into this UDF that uses the datename function ?


create function dbo.tomorrow(@date datetime, @datepart char(4))
returns varchar(12)
as
begin
  return datename(@datepart, @date)
end

Thanks,
Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-08 : 12:15:38

It doesn't appear that DATENAME will allow a variable as the first parameter. So instead of doing it that way, you could use a bunch of IF statements:


create function dbo.tomorrow(@date datetime, @datepart char(4))
returns varchar(12)
as
begin
  IF @datepart = 'yy'
    return datename(yy, @date)
  IF @datepart = 'qq'
    return datename(qq, @date)
...
end

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 12:36:27

I'm curious as to why you would want to make a udf for a function that already exists?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-08 : 17:03:19

Was on a project where a guy created sps
sp_decrement_date - calls sp_increment_date with -1
sp_increment_date - uses dateadd with a -1 or +1 depending on the parameter.

All the updates were done row by row by cursors so he could call the sp to get the new date for the update.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-09-08 : 19:44:56

I didn't have a real need, was just playing with it, and couldn't figure out why it wouldn't take the input parameter.

Thanks,
Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-08 : 19:47:15

It won't take it because you are trying to pass in, as an example:

datename('yy', GETDATE())

but it needs to be:

datename(yy, GETDATE())

The difference is in the single quotes. DATENAME doesn't allow the quotes for the first parameter.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources