Pass inter year and month return last day of month

Question

Post reply

Pass inter year and month return last day of month

adonetok

SSCrazy Eights

Points: 8395
More actions
August 6, 2013 at 12:02 pm

#296479

How to code to pass inter year and month return last day of month?
For example,
pass 2012, 7 will return 07/31/2012
pass 2014, 6 will return 06/30/2014

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mani Subramanian Newbie Points: 6 More actions · Answer 1

Please check if the below script works for you:

DECLARE @y int, @m int

SET @y = 2012 --change year here

SET @m = 7 --change month here

;with date_func as

(

SELECT @y AS [Year],

@m AS [Month],

DATEDIFF(DAY,

DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)),

DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0))

) AS [Days in Month]

)

select cast([Month]as varchar)+'/'+cast([Days in Month]as varchar)+'/'+cast([Year]as varchar) from date_func

Reference:

http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server

adonetok SSCrazy Eights Points: 8395 More actions · Answer 2

adonetok

SSCrazy Eights

Points: 8395

August 6, 2013 at 12:43 pm

#1639073

Works like magic.

Thanks

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Here is another method.

DECLARE @y int = 2012, @m int = 7

select DATEADD(day, -1, dateadd(mm, datediff(mm, 0, cast(@m as varchar(2)) + '/01/' + cast(@y as char(4))) + 1, 0))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

How about this?

WITH SampleData AS (

SELECT Yr=2012, Mon=7, DesiredDate=CAST('07/31/2012' AS DATETIME)

UNION ALL SELECT 2014, 6, CAST('06/30/2014' AS DATETIME))

SELECT Yr, Mon, DesiredDate

,CalcDate=DATEADD(month, Mon, DATEADD(year, Yr-1900, 0))-1

FROM SampleData;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

adonetok (8/6/2013)
How to code to pass inter year and month return last day of month?
For example,
pass 2012, 7 will return 07/31/2012
pass 2014, 6 will return 06/30/2014

Do you actually want a formatted date or just the last day of the month as a DATETIME?

Also, I think I can sense where this is going. If you were to post the proc you're going to use this in, we might be able to make things a little more efficient.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)