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

Question

Post reply

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

Steve Kinnaman

SSC Eights!

Points: 839
More actions
April 1, 2008 at 9:36 am

#188635

I need to add the last day of the month to a result from a column containing only year and month
depending on which month is in the column
the column is called enddate and contains year and month: example: 200803 for march 2008. the column is a varchar(8000)
Select enddate from dbo.sasimp returns:
enddate
200812
200803
200912
201011
I've found som som code that does this but I can't put it together:
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))
The results can be 20081231 or 2008-12-31, it doesn't matter, but I'd preferr the Year-Month-day format
Can anyone help me here?
Steve

Viewing 15 posts - 1 through 15 (of 18 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

select

LastDayOfMonth = dateadd(month,((yr-1900)*12)+mn,-1)

from

(

--Test Data

select yr=2008,mn=1 union all

select yr=2008,mn=2 union all

select yr=2008,mn=3 union all

select yr=2008,mn=4 union all

select yr=2008,mn=5 union all

select yr=2008,mn=6 union all

select yr=2008,mn=7 union all

select yr=2008,mn=8 union all

select yr=2008,mn=9 union all

select yr=2008,mn=10 union all

select yr=2008,mn=11 union all

select yr=2008,mn=12 union all

select yr=2009,mn=1

) a

Results:

LastDayOfMonth

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

2008-01-31 00:00:00.000

2008-02-29 00:00:00.000

2008-03-31 00:00:00.000

2008-04-30 00:00:00.000

2008-05-31 00:00:00.000

2008-06-30 00:00:00.000

2008-07-31 00:00:00.000

2008-08-31 00:00:00.000

2008-09-30 00:00:00.000

2008-10-31 00:00:00.000

2008-11-30 00:00:00.000

2008-12-31 00:00:00.000

2009-01-31 00:00:00.000

(13 row(s) affected)

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 2

I tried below but just get an error, obviously wrong syntax. I tried also putting dbo.sasimp.enddate inside ( but same error. this is my problem, i can't get the syntax right

select

LastDayOfMonth = dateadd(month,((yr-1900)*12)+mn,-1)

from dbo.sasimp.enddate

(

--Test Data

select yr=2008,mn=1 union all

select yr=2008,mn=2 union all

select yr=2008,mn=3 union all

select yr=2008,mn=4 union all

select yr=2008,mn=5 union all

select yr=2008,mn=6 union all

select yr=2008,mn=7 union all

select yr=2008,mn=8 union all

select yr=2008,mn=9 union all

select yr=2008,mn=10 union all

select yr=2008,mn=11 union all

select yr=2008,mn=12 union all

select yr=2009,mn=1

) a

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

This works:

Select Replace(Convert(varchar(22), DateAdd(d, -1, DateAdd(m, 1, getdate())), 102), '.', '')

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

try this:

select enddate, cast((enddate * 100) + 1 as varchar),

dateadd( day, -1,

dateadd( month, 1,

cast(cast((enddate * 100) + 1 as varchar) as smalldatetime)

)

) as endOfMonth

from

(select 200812 as enddate

union select 200803

union select 200912

union select 201011) as data

GSquared SSC Guru Points: 260824 More actions · Answer 5

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.

- 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

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 6

Steve Kinnaman

SSC Eights!

Points: 839

April 1, 2008 at 1:25 pm

#796823

Gsquared, your tip works perfectly

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

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

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

shamshudheen SSCrazy Points: 2390 More actions · Answer 8

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

Jeff

the query you posted is throwing error "Operand type clash: INT is incompatible with DATETIME "

i tryed the following with lite change in your posted query

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

FROM

( SELECT '200812' AS EndDate

UNION ALL SELECT '200803'

UNION ALL SELECT '200912'

UNION ALL SELECT '201011') a

its result is

Dec 31 2008 12:00AM

Mar 31 2008 12:00AM

Dec 31 2009 12:00AM

Nov 30 2010 12:00AM

cheers

shamsudheen

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 9

Jeff Moden (4/1/2008)

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

And I've always been trying to avoid implicit conversions ......... :hehe:

In the xml-plan it shows ....

ScalarOperator ScalarString="dateadd(month,(1),CONVERT_IMPLICIT(datetime,[Union1004]+'01',0))-'1900-01-02 00:00:00.000'"

When using

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

the xml-plan it shows ....

ScalarOperator ScalarString="dateadd(month,(1),CONVERT(datetime,[Union1004]+'01',0))-'1900-01-02 00:00:00.000'"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (4/2/2008)
And I've always been trying to avoid implicit conversions

Heh... understood. I don't think they make a hoot in the SELECT list... but, I guess I'm gonna have to do a million row test and see if it does. You could very well be right... thanks for the reminder.

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

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

ALZDBA (4/2/2008)

And I've always been trying to avoid implicit conversions ......... :hehe:

Apparently, even on a million rows... makes no moxnix...

DECLARE @Bitbucket datetime
SET STATISTICS TIME ON
SELECT @Bitbucket = DATEADD(mm,1,EndDate+'01')-1
FROM dbo.jbmtest
SELECT @Bitbucket = DATEADD(mm,1,CAST(EndDate+'01' AS DATETIME))-1
FROM dbo.jbmtest
SET STATISTICS TIME OFF

[font="Courier New"]SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 11 ms.

SQL Server Execution Times:

CPU time = 2937 ms, elapsed time = 2976 ms.

SQL Server Execution Times:

CPU time = 2947 ms, elapsed time = 2947 ms.[/font]

... or at least it makes no difference performance wise in the SELECT list...

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 12

Thank you Jeff for the proformance comparison :rolleyes:

Especialy in that case ( it's only a neglectable difference) I'd prefer the use of the cast or convert, just to avoid implicit conversions and because of the best practise "Tell the system what you know".

We've all experienced the impact that implicit conversions may have regarding index usage, ..

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Very good point.

Actually, I'm not even sure why I posted the implicit conversion code... Implicit conversions constitute a "default" of sorts and I've seen MS change a couple of defaults in way to many apps... no reason why they wouldn't someday do the same in SQL Server. The explicit conversion would prevent code failure due to change in defaults and it would as the next programmer, who might not understand the defaults, in figuring out the code.

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 14

must have been a moment of weakness :w00t:

For once it is ok :hehe: :laugh:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me