We help IT Professionals succeed at work.

TSQL Scalar Function using select inside the function & excluding date

Leogal
Leogal used Ask the Experts™
on
I apologize in advance for asking what is likely a basic question, problem is that I am new to SQL and having difficulties with the syntax.

I am writing this function:

Alter function dbo.Fn_get_last_day
(@FN_InputDt               datetime

Returns      @d
as
begin

declare @d    datetime
    set    @d  = @FN_InputDt

select @d =
                   case when @FN_InputDt = 0 then 0 else
                            select day (@Fn_InputDt) DatePart(d,@FN_InputDt)
                    end

Return

End

Here are the errors returned:
Msg 156,  Level 15, State 1, Procedure FN_GET_LAST_DAY , line 16
incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure FN_GET_LAST_DAY, line 16
Incorrrect syntax near 'D'
Msg 102, Level 15, State 1, Procedure FN_GET_LAST_DAY, line 21
Incorrect syntax near 'END'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
create function dbo.Fn_get_last_day
(@FN_InputDt               datetime)
Returns      int
as
begin
declare @d    int
select @d =
                   case when @FN_InputDt = 0 then 0 else
                            day (@Fn_InputDt)
                            --datePart(d,@FN_InputDt)   -- one or the other but not both
                    end
Return @d
End

Author

Commented:
Cyberkiwi, It works great, the problem is I am still not getting the last date of the month , ie  30, 31, 28 or the like
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Now I see what you are after.

alter function dbo.Fn_get_last_day (@FN_InputDt datetime)
returns int
as
begin
return case when @FN_InputDt=0 then 0 else day(dateadd(m, 1+datediff(m, 0, @FN_InputDt), 0)-1) end
end

X = datediff(m, 0, @FN_InputDt) -- calculates how many months have elapsed from "virtual date 0"
Y = dateadd(m, 1+X, 0) -- adds 1+X months to the "virtual date 0", so we are in the next month (X+1)
Note: the side effect is that it also set the day-in-month to 1st day of month
Z = Y-1  -- take one day off, so we're back in this month, at the last day
Day(Z)   --- get the day of month

Author

Commented:
Cyberwiki, Thanks so very much, I sure appreciate your explanation. it is so helpful! . I really cannot say thank you enough for assistance and patience.

Author

Commented:
the folks on here are the great!