I need to add the last day of the month to a result from a column containing only year and month

Question

I need to add the last day of the month to a result from a column containing only year and month

Viewing 4 posts - 16 through 18 (of 18 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Jeff Moden (4/1/2008)
GSquared (4/1/2008)
select dateadd(month, 1, cast(enddate + '01' as datetime)) - 1 from dbo.sasimp
Should give you what you need. If it gives you an error about converting enddate to varchar, try this:
select dateadd(month, 1, cast(cast(enddate as varchar(6)) + '01' as datetime)) - 1
from dbo.sasimp
But leave out the extra "cast" unless you find you actually need it.
You don't need any of the CASTs...
SELECT DATEADD(mm,1,EndDate+'01')-1
FROM (
SELECT '200812' AS EndDate UNION ALL
SELECT '200803' UNION ALL
SELECT '200912' UNION ALL
SELECT '201011'
) testdata

You're right that it's not necessary in this case, but I try to avoid implicit conversions as a practice, because I find that it makes it easier to find buggy code later on.

If I get an error from a proc that's been running for 6 months, that says "error converting type X to type Y", and it's an implicit conversion, it's sometimes harder to find than if I can go into the proc, hit Ctrl+F, and find "cast" or "convert".

It's not a big deal, but it's a standard I hold myself to because it has saved me time when it's been critical a couple of times.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I agree... like I said, not sure why I even posted the implicit code... I don't trust defaults for the same reasons you don't.

--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)

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 3

Just wanted to say a quick thanks your code, M V Jones - I was able to use it to get exactly what I needed in a much simpler and more elegant way than I had originally planned for!

Thanks again,

Simon

Michael Valentine Jones (4/1/2008)
select LastDayOfMonth = dateadd(month,((yr-1900)*12)+mn,-1) from ...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

shamshudheen (4/2/2008)
Jeff
the query you posted is throwing error "Operand type clash: INT is incompatible with DATETIME "

Then, you're done something wrong... Int IS compatabible with DATETIME in SQL Server. The following code "copied from the original" works just fine...

SELECT DATEADD(mm,1,EndDate+'01')-1

FROM (

SELECT '200812' AS EndDate UNION ALL

SELECT '200803' UNION ALL

SELECT '200912' UNION ALL

SELECT '201011') testdata

------------------------------------------------------

2008-12-31 00:00:00.000

2008-03-31 00:00:00.000

2009-12-31 00:00:00.000

2010-11-30 00:00:00.000

(4 row(s) affected)

--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)