A bit of help!!! - 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 2005 Forums

Transact-SQL (2005)

A bit of help!!!

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 06:54:23

Hi

I am trying to create a function using the following SQL

CREATE FUNCTION DatePeriodID
BEGIN
SELECT
CAST(DATEPART(yy,ID)AS NVARCHAR(4)) + '-' + CONVERT(NVARCHAR(2),ID, 101)
FROM dbo.DatePeriods
END

I keep on getting the message:
Msg 156, Level 15, State 1, Procedure udf_DatePeriodID, Line 2
Incorrect syntax near the keyword 'BEGIN'.

I've tried editing it but I keep on getting an error.

Can anyone help?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 07:05:38

quote:
Originally posted by rcr69er

Hi

I am trying to create a function using the following SQL

CREATE FUNCTION dbo.DatePeriodID()
RETURNS TABLE
As
Return(SELECT
CAST(DATEPART(yy,ID)AS NVARCHAR(4)) + '-' + CONVERT(NVARCHAR(2),ID, 101) as ID
FROM dbo.DatePeriods)

I keep on getting the message:
Msg 156, Level 15, State 1, Procedure udf_DatePeriodID, Line 2
Incorrect syntax near the keyword 'BEGIN'.

I've tried editing it but I keep on getting an error.

Can anyone help?

Thanks

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 07:07:39

Hi

Thanks, but i'm still getting the same message!!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 07:09:53

See again Edited post.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 07:14:25

Hey

Thanks, Great Stuff!!!

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 07:27:30

Sorry to be a pain, but how do you select the fucntion? Is it simply Select [DatePeriodID]?

Sorry I'm new to using functions

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 07:50:25

select * from dbo.DatePeriodID()

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 07:50:34

Hi
Is it possible to do the function as scalar-valued function as opposed to a table-valued one.

I'm having a bit of trouble calling it as a table-valued one

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 07:54:14

Yes.
I think you better tell us what you are trying to accomplish. Otherwise we can give you fragments of help all day long.

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 07:57:50

Hi

I basically want to view the output of the function through a Select statement, so it should display something like:
2008-05
2008-06
2007-05
2007-06

I hope this helps

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 08:01:49

In that case table-valued function is what you need. But if you are writing function for as simple as converting date to some format, you can do that inline the query without using functions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 08:16:44

Simply use CONVERT(CHAR(7), GETDATE(), 120) to get "yyyy-mm" format of a datetime value.

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 09:01:46

Hi

Sorry my mistake!

I wish to apply the function to date value within the table. So using the syntax Peso provided how would I put this into a function?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 09:06:12

Why you want to use function for such a simple formatting issue?

Replace GETDATE() with the column name from your table using the solution given by the Peso to get the desired output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 09:12:50

Hi

Its a requirement that I've been given to find out.
Is it possibe?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-20 : 09:18:04

http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/format-date-sql-server.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 09:18:33

quote:
Originally posted by rcr69er

Hi

Its a requirement that I've been given to find out.
Is it possibe?

Didnt you understood what Harsh suggested? Replace GETDATE() in eralier query with your date column.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 09:23:07

Hi

Yes I understood it, I just need to know how to put it into a function sytnax, ie.

Create Function DateID
...
...
SELECT CONVERT(CHAR(7), GETDATE(), 120)
...

I hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 09:29:39

quote:
Originally posted by rcr69er

Hi

Yes I understood it, I just need to know how to put it into a function sytnax, ie.

Create Function DateID
...
...
SELECT CONVERT(CHAR(7), GETDATE(), 120)
...

I hope this helps

Why do you think you need to put this in a function? Are you performing any other operations inside function?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 09:32:07

At present no, its just a requirement that someone has asked me to do. And they wished it to be held within a function.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 09:38:33

quote:
Originally posted by rcr69er

At present no, its just a requirement that someone has asked me to do. And they wished it to be held within a function.

Thanks

If its only thing you're doing, its better to put it inline in your query rather than wrapping in a function and calling it. This is exactly what Harsh suggested earlier.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources