So I have borrowed from these boards and modified a function for End of Month as seen below. As you can see, I've set the input up as varchar 24, so it can accept anything from '2012-05' to 20120630 to '2012-05-27 00:00:00.000', and it does so swimmingly.
What I'm wodnering is that I know I've read that in later versions of SQL the output format is whatever format the input is in, however when I code the function myself I select a single format for output (here it's datetime, but could be anything). I'd like to know how I can setup my script so that it returns whatever format it came in as.
In other words, if I do select EOMONTH(20120614) I want to get 20120630 and if I input EOMONTH('2012-02-17 00:00:00.000) I want to return 2012-02-29 00:00:00.000.
Am I missing something simple on how to do this?
Alter FUNCTION EOMONTH
( @Date varchar(24) )
--ensure valid date
IF ISDATE(@Date) = 1
--determine first day of month
SET @Date = DATEADD(day,-DAY(@Date)+1,@Date)
--determine last day of month
SET @Date = DATEADD(day,-1,DATEADD(month,1,@Date))
SET @Date = '1/1/80'