Fiscal or Calendar Year Function error

Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscal
Hi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Fiscal or Calendar Year Function error

Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal = CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Fiscal or Calendar Year Function error

Hello,
I am trying to create a function that returns the first day of the fiscal or
calendar year depending on the parameter supplied. However, I get the
error: Error 443: Invalid use of the 'getdate' within a function.
Any help would be appreciated.
Thanks in advance, sck10
CREATE FUNCTION dbo.fn_GetCalendarFiscalYear (@.YearType varchar(100))
RETURNS varchar(255)
AS
BEGIN
Declare @.strCalendarFiscal varchar(255)
SELECT @.strCalendarFiscal =
CASE
WHEN @.YearType = 'calendar' Then '1/1/' + Convert(varchar(255),
year(getdate()))
WHEN @.YearType = 'fiscal' Then
CASE
WHEN month(getdate()) BETWEEN 10 AND 12 THEN
'10/1/' + Convert(varchar(255), year(getdate()))
ELSE
'10/1/' + Convert(varchar(255), year(getdate()) - 1)
END
ELSE 'Pick "calendar" or "fiscal"'
END AS 'strFiscalYearStart'
RETURN @.strCalendarFiscalHi,
Welcome to use MSDN Managed Newsgroup!
I am so sorry that you can not put a function that returns a varible
result in a user defined function. Check MVP Aaron Bertrand's article for
this issue
How do I use GETDATE() within a User-Defined Function (UDF)?
http://www.aspfaq.com/2439
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

FirstWeekOfYear as parameter of DatePart

Haw to make FirstWeekOfDay parameter work in SQL Server 2000.
In Access 2000 datepart function has 3 parameters.
Why it is't in SQL Server 2000.
Help pleaseStraight from Books Online:

SET DATEFIRST
Sets the first day of the week to a number from 1 through 7.

Syntax
SET DATEFIRST { number | @.number_var }

Arguments
number | @.number_var

Is an integer indicating the first day of the week, and can be one of these values.

Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday

Remarks
Use the @.@.DATEFIRST function to check the current setting of SET DATEFIRST.

The setting of SET DATEFIRST is set at execute or run time and not at parse time.

Permissions
SET DATEFIRST permissions default to all users.

Examples
This example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
GO

First/Last Day of Month (current and previous three months)

Hi,
I want to set a parameter to use the first and last days of the month for
two parameters' defaults. I then want to set the first day of the previous
two months as the "available values" for on of the parameters and the last
day of the previous two months for the other parameter's "available values".
Can anyone tell me why this isn't working (I want to get last day of month):
=DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
I have tried using the Today funcion instead of Now with no joy. I started
doing this with DATEADD and DATEDIFF, as is commonly found as the solution
for this on tons of tech articles. However, I have learnt that although
T-SQL can work out what you want to do with DATEADD (which expects DateTime)
and DATEDIFF(which returns an Integer), reporting services cannot. So I
found the above expression that doesn't use DATEDIFF, but I still can't get
it to work. The expression builder doesn't seem to find a problem with the
syntax (no green or red wavy lines), but when I run the report I get the
following:
An error occurred duing local report processing. Error during processing of
'SDate' report parameter.
Please tell me someone has an answer for me. This should be so simple. I
bet I am going to kick myself when (if!!) the answer comes through.
TIA,
JarrydHi,
Well I just did this in T-SQL as part of the procedure that feeds the
report. Created a virtual table and added a second dataset to the report.
That seems to work. One thing I find odd - the "calendar" button can't be
used to set the date properly. We are in the UK and so the calendar
generates a UK style date, but Reportiong Services won't have it; you have
to manually enter it in USA format!! How do you solve it? Please don't
tell me you have to programtically grab the variable and cast it in USA
style. That's just silly. But if so, then where do you do it? I am
assuming you have to go to Dataset>Parameters and jimmy the value field's
value (def: Parameters!My_Param.Value) to grab the value and reorder the
days and months but I can't get it to work. The only other place I know of
is the Report>Report Parameters form, but that doesn't look too promising.
TIA,
Jarryd
"Jarryd" <noemail@.nodomain.com> wrote in message
news:u4I1mEMqHHA.2372@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to set a parameter to use the first and last days of the month for
> two parameters' defaults. I then want to set the first day of the
> previous two months as the "available values" for on of the parameters and
> the last day of the previous two months for the other parameter's
> "available values".
> Can anyone tell me why this isn't working (I want to get last day of
> month):
> =DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
> I have tried using the Today funcion instead of Now with no joy. I
> started doing this with DATEADD and DATEDIFF, as is commonly found as the
> solution for this on tons of tech articles. However, I have learnt that
> although T-SQL can work out what you want to do with DATEADD (which
> expects DateTime) and DATEDIFF(which returns an Integer), reporting
> services cannot. So I found the above expression that doesn't use
> DATEDIFF, but I still can't get it to work. The expression builder
> doesn't seem to find a problem with the syntax (no green or red wavy
> lines), but when I run the report I get the following:
> An error occurred duing local report processing. Error during processing
> of 'SDate' report parameter.
> Please tell me someone has an answer for me. This should be so simple. I
> bet I am going to kick myself when (if!!) the answer comes through.
> TIA,
> Jarryd
>

first day of previous month date parameter

Hi,
Hope somebody can help.
I am looking for the code to place in the default value of a parameter wich
will give me the previous months first days date (i.e. last month would be
01/03/2005).
I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
so something similar to this would be great.
Thanks in advance.
Paul=DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
Month(DateAdd(DateInterval.Month, -1, Now)), 1)
Charles Kangai, MCDBA, MCT
"pcalv" wrote:
> Hi,
> Hope somebody can help.
> I am looking for the code to place in the default value of a parameter wich
> will give me the previous months first days date (i.e. last month would be
> 01/03/2005).
> I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> so something similar to this would be great.
> Thanks in advance.
> Paul|||That worked great, thanks for your help.
Paul
"Charles Kangai" wrote:
> =DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
> Month(DateAdd(DateInterval.Month, -1, Now)), 1)
> Charles Kangai, MCDBA, MCT
> "pcalv" wrote:
> > Hi,
> >
> > Hope somebody can help.
> >
> > I am looking for the code to place in the default value of a parameter wich
> > will give me the previous months first days date (i.e. last month would be
> > 01/03/2005).
> >
> > I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> > so something similar to this would be great.
> >
> > Thanks in advance.
> >
> > Paul