Need SQL function to output two dates

Hi
I have a query which outputs a pair of dates based upon the current date - it works just fine but I need to put it into a Function and need help - here is my attempt at writing the Function - thanks for the help!

CREATE FUNCTION [dbo].[imaps_fn_date2intelcalrng](@intl_mo_strt, @intl_mo_end)

RETURNS DATETIME
AS
BEGIN

	DECLARE @intl_mo_strt SMALLDATETIME
	DECLARE @intl_mo_end DATETIME	

		SELECT DISTINCT MIN([intel_strt_dtm]) AS intl_mo_strt, MAX([intel_end_dtm]) AS intl_mo_end
		FROM [dbo].[imaps_intel_clndr]
		WHERE CAST(intel_yr AS CHAR(4)) + CASE WHEN LEN(intel_mo) = 1	THEN '0' + CAST(intel_mo AS CHAR(2)) ELSE CAST(intel_mo AS CHAR(2)) END = 
			(
				SELECT DISTINCT CAST(intel_yr AS CHAR(4)) + CASE WHEN LEN(intel_mo) = 1	THEN '0' + CAST(intel_mo AS CHAR(2)) ELSE CAST(intel_mo AS CHAR(2)) END AS YearMonth
				FROM  [dbo].[imaps_intel_clndr]
				WHERE [intel_strt_dtm] = CONVERT(VARCHAR, GETDATE(),101)
			)
   RETURN(@intl_mo_strt, @intl_mo_end)
END

Open in new window

fjkilkenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me check out, but to return 2 values, you cannot use RETURN DATETIME, as that is 1 single value.
you need to get a table-valued return type instead.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
code sample:

CREATE FUNCTION [dbo].[imaps_fn_date2intelcalrng](@intl_mo_strt SMALLDATETIME, @intl_mo_end SMALLDATETIME)

RETURNS @t TABLE  ( intl_mo_strt SMALLDATETIME, intl_mo_end DATETIME )
AS
BEGIN

	insert into @t 
		SELECT DISTINCT MIN([intel_strt_dtm]) AS intl_mo_strt, MAX([intel_end_dtm]) AS intl_mo_end
		FROM [dbo].[imaps_intel_clndr]
		WHERE CAST(intel_yr AS CHAR(4)) + CASE WHEN LEN(intel_mo) = 1	THEN '0' + CAST(intel_mo AS CHAR(2)) ELSE CAST(intel_mo AS CHAR(2)) END = 
			(
				SELECT DISTINCT CAST(intel_yr AS CHAR(4)) + CASE WHEN LEN(intel_mo) = 1	THEN '0' + CAST(intel_mo AS CHAR(2)) ELSE CAST(intel_mo AS CHAR(2)) END AS YearMonth
				FROM  [dbo].[imaps_intel_clndr]
				WHERE [intel_strt_dtm] = CONVERT(VARCHAR, GETDATE(),101)
			)
   RETURN 
END

Open in new window


here we go to call it:
select * from [dbo].[imaps_fn_date2intelcalrng](getdate(), getdate())

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fjkilkenAuthor Commented:
perfect - thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.