Scalar Function - SYSDATETIME() as default parameter.

Question

Post reply

Scalar Function - SYSDATETIME() as default parameter.

Koen Verbeeck

SSC Guru

Points: 259115
More actions
August 22, 2014 at 6:46 am

#397242

I have the following scalar function to calculate business hours:
CREATE FUNCTION dbo.business_hours
(
@in_start_dtDATETIME2(7)= SYSDATETIME()
,@in_end_dtDATETIME2(7)= SYSDATETIME()
)
RETURNS NUMERIC(10,2)
BEGIN
-- business_hours returns the number of work hours (8.30 am through 5.30 pm - which means 9 business hours per day -
-- Monday through Friday) between in_start_dt and in_end_dt.
-- If in_start_dt > in_end_dt, the results will be <= 0.
DECLARE @dNUMERIC(10,2); -- Hours of either start_dt or end_dt after midnight
DECLARE @end_dtDATETIME2(7) = IIF(@in_start_dt < @in_end_dt,@in_end_dt,@in_start_dt); -- In case dates were in wrong order
DECLARE @start_dt DATETIME2(7) = IIF(@in_start_dt < @in_end_dt,@in_start_dt,@in_end_dt); -- In case dates were in wrong order
DECLARE @return_val NUMERIC(10,2); -- Total number of working hours
WITH CTE_Numbers AS -- tally table
(
SELECT TOP(100000) RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- start with 0
FROM sys.COLUMNS c1
CROSS JOIN sys.COLUMNS c2
)
,all_days AS
(
SELECT a_dt = CONVERT(DATETIME2(7),DATEADD(DAY,RID,CONVERT(DATE,@start_dt)))
FROM CTE_Numbers
WHERE DATEADD(DAY,RID,CONVERT(DATE,@start_dt)) <= CONVERT(DATE,@end_dt)
)
SELECT @return_val = SUM(9) -- 9 business hours in a day
FROM all_days
WHERE DATENAME(WEEKDAY,a_dt) NOT IN ('Saturday', 'Sunday');
-- Adjust hours from start_dt, if necessary
IF (DATENAME(WEEKDAY,@start_dt) NOT IN ('Saturday', 'Sunday'))
BEGIN
IF (CONVERT(TIME,@start_dt) >= CONVERT(TIME,'17:30:00'))-- Don't count start_dt itself
SET @return_val = @return_val - 9;
ELSE
BEGIN
IF (CONVERT(TIME,@start_dt) > CONVERT(TIME,'8:30:00'))-- Don't count part of start_dt (hours of midnight till 8:30)
SET @return_val = @return_val - (DATEPART(HOUR,@start_dt) + (DATEPART(MINUTE,@start_dt) / 60.0) + (DATEPART(SECOND,@start_dt) / 3600.0) - 8.5)
END
END
-- Adjust hours from end_dt, if necessary
IF (DATENAME(WEEKDAY,@end_dt) NOT IN ('Saturday', 'Sunday'))
BEGIN
IF (CONVERT(TIME,@end_dt) <= CONVERT(TIME,'8:30:00'))-- Don't count end_dt itself
SET @return_val = @return_val - 9;
ELSE
BEGIN
IF (CONVERT(TIME,@end_dt) < CONVERT(TIME,'17:30:00'))-- Don't count part of end_dt (hours of end_dt till 17:30)
SET @return_val = @return_val - (17.5 - (DATEPART(HOUR,@start_dt) + (DATEPART(MINUTE,@start_dt) / 60.0) + (DATEPART(SECOND,@start_dt) / 3600.0)));
END
END
IF (@in_start_dt > @in_end_dt) -- If in_start_dt > in_end_dt, the results will be <= 0.
SET @return_val = -@return_val;
RETURN @return_val;
END
However, when I try to create the function, I get the following errors:
Msg 102, Level 15, State 1, Procedure business_hours, Line 3
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure business_hours, Line 12
Must declare the scalar variable "@in_start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 13
Must declare the scalar variable "@in_start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 24
Must declare the scalar variable "@start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 34
Must declare the scalar variable "@start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 36
Must declare the scalar variable "@start_dt".
Msg 156, Level 15, State 1, Procedure business_hours, Line 38
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Procedure business_hours, Line 40
Must declare the scalar variable "@start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 41
Must declare the scalar variable "@start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 46
Must declare the scalar variable "@end_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 48
Must declare the scalar variable "@end_dt".
Msg 156, Level 15, State 1, Procedure business_hours, Line 50
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Procedure business_hours, Line 52
Must declare the scalar variable "@end_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 53
Must declare the scalar variable "@start_dt".
Msg 137, Level 15, State 2, Procedure business_hours, Line 57
Must declare the scalar variable "@in_start_dt".
Msg 178, Level 15, State 1, Procedure business_hours, Line 60
A RETURN statement with a return value cannot be used in this context.
When I ditch the brackets behind SYSDATETIME, I can create the function:
CREATE FUNCTION dbo.business_hours
(
@in_start_dtDATETIME2(7)= SYSDATETIME
,@in_end_dtDATETIME2(7)= SYSDATETIME
)
RETURNS NUMERIC(10,2)
BEGIN
...
END
However, when I call the function like this:
SELECT dbo.business_hours(DEFAULT,DEFAULT)
I get the following error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Calling it like this works:
SELECT [dbo].[business_hours](SYSDATETIME(),SYSDATETIME())
Anyone an idea?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Found it 😀

Apparently default values for parameters need to be constants, not other functions.

http://stackoverflow.com/questions/470664/sql-function-as-default-parameter-value

Boohoo SQL Server.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

Koen Verbeeck (8/22/2014)
Found it 😀
Apparently default values for parameters need to be constants, not other functions.
http://stackoverflow.com/questions/470664/sql-function-as-default-parameter-value
Boohoo SQL Server.

I'm sure it's obvious to you but, in case anyone else reads this, you could assign the default values to be NULL and then reassign the variables to SYSDATETIME() if they're NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Jeff Moden (8/22/2014)
Koen Verbeeck (8/22/2014)
Found it 😀
Apparently default values for parameters need to be constants, not other functions.
http://stackoverflow.com/questions/470664/sql-function-as-default-parameter-value
Boohoo SQL Server.
I'm sure it's obvious to you but, in case anyone else reads this, you could assign the default values to be NULL and then reassign the variables to SYSDATETIME() if they're NULL.

Yeah, that's what I ended up doing 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP