January 13, 2014 at 1:11 pm
Hello I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. Any ideas on how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
January 13, 2014 at 1:26 pm
ccmret (1/13/2014)
Hello I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. Any ideas on how to make this work?Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
What seems to be your problem? Are you getting an error message? If so, what is it?
January 13, 2014 at 1:32 pm
I can't find the reference (so I might be wrong), but I'm sure you can't assign values from a function when declaring parameter defaults.
EDIT: Reference seems to be only for CREATE PROCEDURE where it states: "The default value must be a constant or it can be NULL". However, this remark is not made in CREATE FUNCTION.
January 13, 2014 at 1:35 pm
I get incorrect syntax near keyword CONVERT and Must declare the scalar variable @startdate
January 13, 2014 at 2:11 pm
Luis pointed it out i think; the assignment must be a static value or constant;
a convert cannot be used, even if it was a static date, nore getdate,
and when you call it with defaults, you'd need to use the default keyword, since the parameters are manditory for a function
something like this looks like it is along the lines of what you need:
Create Function HR.Equipment
(
@startdate Date =NULL,
@enddate Date =NULL
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=ISNULL(@StartDate,DATEADD(DAY,-1,GETDATE())) and IssueDate >=ISNULL(@enddate,DATEADD(DAY,-90,GETDATE()))
)
Lowell
January 13, 2014 at 2:26 pm
This seemed to work Lowell. Thanks a bunch.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy