# Scarlar Value Function Syntax error (probably)

Righto Experts

Pigeon steps.....Am trying to set myself up my first written Scarlar Function and I keep getting error messeges.

The current one is :
Msg 102, Level 15, State 1, Procedure FiscalWk, Line 48
Incorrect syntax near '@FsWks'.

The script is meant to work out Fiscal week and year utilising a set date (defined from the given @startMonth variable)

Now I'm sure there is a more efficient way of processing this information (pointers are always greatly recived :) ) but I am seeking help in just getting this function to work please.

``````Create function [dbo].[FiscalWk] (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @dy1 datetime
declare @dynm int
declare @year int
declare @Fsyr int
declare @FsWks int
declare @mnth int

--Gets mnth from declared Mydate
Set @mnth = datepart(month,@mydate)
--Gets yr from declared mydate
Set @year = datepart(year, @myDate)
--Works out Fiscal Year
Set @Fsyr = Case
when @mnth = 1 then @year-1
else @year
End
--gets 1st date of fiscal year from mydate and start month from delcaration in controlling table (MainCli)
Set @dy1 = convert(datetime,@year + @startMonth + '01',103)
-- gets day number of 1st day of year ie 1 = Monday
Set @dynm = datepart(dw,@dy1)
-- works out integer to as Monday = 1st day of fiscal year then that would be correct, but if it was a Tuesday then that would be 6 days after
--the 1st week of a year

When @dynm = 1 then 0
When @dynm = 2 then 6
When @dynm = 3 then 5
When @dynm = 4 then 4
When @dynm = 5 then 3
When @dynm = 6 then 2
When @dynm = 7 then 1
End

return @FsWks
``````
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
the END and a @ (before dtadj in the last expression) were missing
``````Create function [dbo].[FiscalWk] (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @dy1 datetime
declare @dynm int
declare @year int
declare @Fsyr int
declare @FsWks int
declare @mnth int

--Gets mnth from declared Mydate
Set @mnth = datepart(month,@mydate)
--Gets yr from declared mydate
Set @year = datepart(year, @myDate)
--Works out Fiscal Year
Set @Fsyr = Case
when @mnth = 1 then @year-1
else @year
End
--gets 1st date of fiscal year from mydate and start month from delcaration in controlling table (MainCli)
Set @dy1 = convert(datetime,@year + @startMonth + '01',103)
-- gets day number of 1st day of year ie 1 = Monday
Set @dynm = datepart(dw,@dy1)
-- works out integer to as Monday = 1st day of fiscal year then that would be correct, but if it was a Tuesday then that would be 6 days after
--the 1st week of a year

When @dynm = 1 then 0
When @dynm = 2 then 6
When @dynm = 3 then 5
When @dynm = 4 then 4
When @dynm = 5 then 3
When @dynm = 6 then 2
When @dynm = 7 then 1
End

Set @FsWks = ( Round(((   ( Datediff(day,@dy1,@mydate) -@dtadj)/7)  +2),2))+@Fsyr
return (@FsWks)
End
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Fan-dabby-Dozie

Cheers Angelll

I fear the syntax spectre will rear its ugly head again before long..can't wait

:)

0
Commented:
Now I'm sure there is a more efficient way of processing this information (pointers are always greatly recived :)
If you don't mind a pointer: If you are doing  lot of reporting then its a good idea to have a calendar table. This has a unique indexed key on date which has every date you could possibly report on, then it has a whole host of fields which slot the date into a particular grouping
CalendarDate
Financial Year
Financial Month
Financial Week
Calendar Year
Calendar Month
Calendar Week
Plus a bunch of boolean type fields:
IsPublicHoliday
IsWeekend
IsYTD
IsMTD
YTD and MTD type fields are update by a daily process.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.