Function return type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function return type

Author

Topic

rdw72777
Starting Member

14 Posts

Posted - 2012-07-27 : 15:16:23

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) )
RETURNS datetime
AS
BEGIN
 --ensure valid date
 IF ISDATE(@Date) = 1
  BEGIN
   --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))
  END
 ELSE
  SET @Date = '1/1/80'

  RETURN @Date
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-27 : 15:30:11

[code]ALTER FUNCTION EOMONTH
(
@Date VARCHAR(24)
)
RETURNS DATETIME
AS
BEGIN
SELECT CASE ISDATE(@Date)
WHEN 1 THEN DATEADD(MONTH, DATEDIFF(MONTH, '19791231', CAST(@Date AS DATETIME)), '19791231')
ELSE '19800101'
END
END[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 15:32:50

question is why you cant do this at front end. why change datatype itself for formatting purpose which will make it a real pain to work with these values if you require any further manipulation on them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-27 : 15:33:19

There is no way to satisfy your request. What if you enter the date value '03/03/2012' ?
Should the return value be '03/31/2012' or '31/03/2012' ?

N 56°04'39.26"
E 12°55'05.63"

rdw72777
Starting Member

14 Posts

Posted - 2012-07-27 : 15:49:41

What do you mean at the front end? I want this to be usable with various data types because for whatever reason we use just about every datatype you can think of in our myriad of tables.

So the function could conceivably receive inputs in numerous datatyopes but the way iw rote it it can only output in just one. I know I've read SQL2012 EOMONTH builtin can check the datatype input and output the same type.

I could script a few different versions of the same function (EOMONTH_YYYYMMDD, EOMONTH_DateTime, etc) but was wondering if there was a prettier way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 16:08:35

quote:
Originally posted by rdw72777

What do you mean at the front end? I want this to be usable with various data types because for whatever reason we use just about every datatype you can think of in our myriad of tables.

So the function could conceivably receive inputs in numerous datatyopes but the way iw rote it it can only output in just one. I know I've read SQL2012 EOMONTH builtin can check the datatype input and output the same type.

I could script a few different versions of the same function (EOMONTH_YYYYMMDD, EOMONTH_DateTime, etc) but was wondering if there was a prettier way.

then its upto you.
but if you want to use the values returned by function for further date manipulation you might require again converting them back to date datatype.

front end means application where you show the date values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources