User Defined Function that returns Start Date and End Date

Question

Post reply

User Defined Function that returns Start Date and End Date

ganteng1

Ten Centuries

Points: 1183
More actions
October 15, 2014 at 3:37 am

#311815

Hi Everyone.
Does anyone have a UDF that accepts a Month and Year and returns Start Date and End Date.
For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.
Thanks for your help

Viewing 9 posts - 1 through 8 (of 8 total)

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

Andreas P. Williams SSCrazy Points: 2487 More actions · Answer 1

Just to get you started: have look at the DATEADD function (http://msdn.microsoft.com/de-de/library/ms186819.aspx).

You can make use of the fact that 0 corresponds to 01-01-1900.

A final hint: The last day of the month is one day before the first day of the next month.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Use dates functions, dateadd() namely.

declare @Month varchar = '01'

declare @Year varchar = '2014'

select starDt = CAST(@Year + @Month +'01' as Date), endDt = dateadd(dd,-1,dateadd(mm,1, CAST(@Year + @Month +'01' as Date)))

Or the question is about UDF syntax?

ganteng1 Ten Centuries Points: 1183 More actions · Answer 3

ganteng1

Ten Centuries

Points: 1183

October 15, 2014 at 4:02 am

#1752437

Thanks for that. Can you please provide as UDF?

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

Look at inline TVF http://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx

ganteng1 Ten Centuries Points: 1183 More actions · Answer 5

Thanks for that. I tried the following code below but it returned the wrong answer. Do you have any further suggestions:

declare @Month varchar = '02'

declare @Year varchar = '2014'

select StartDate = CAST(@Year + @Month +'01' as Date)

,endDt = dateadd(dd,-1,dateadd(mm,1, CAST(@Year + @Month +'01' as Date)))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

ganteng1 (10/15/2014)
Thanks for that. I tried the following code below but it returned the wrong answer. Do you have any further suggestions:
declare @Month varchar = '02'
declare @Year varchar = '2014'
select StartDate = CAST(@Year + @Month +'01' as Date)
,endDt = dateadd(dd,-1,dateadd(mm,1, CAST(@Year + @Month +'01' as Date)))

You forgot to size your variables. CHAR is more appropriate also:

DECLARE @Month CHAR(2) = '02'

DECLARE @Year CHAR(4) = '2014'

SELECT

StartDate,

EndDt = DATEADD(dd, -1, DATEADD(mm, 1, StartDate))

FROM (SELECT StartDate = CAST(@Year + @Month +'01' AS DATE)

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden