Query to get the last date of the previous month.

I need to run a query which would return the last date of the month. Suppose i am running this query in May i need to get the result as 20080430 i.e. 30th april 2008. Is there any query which could extract this?

I just like to do stuff different than all others =;o)select
dateadd(day, -1, convert(char(6), getdate(), 112) + '01')
/Kenneth

Frank Kalis version is faster. No varchar convertion.

Thanks guys it returned the result that i wanted but i want it in this format 20080430. The query that you both sent returns it as
2008-04-30 00:00:00.000.

[quote user="pedrolopezmail"]
Frank Kalis version is faster. No varchar convertion.
[/quote]
That is true, there is no conversion in Frank's version.
If that is a difference that is noticable, however, is another question.
In my experince, the added conversion overhead is between neglible and non-existant.
(note that we are talking about display here)
Anyways, I like my version simply because I think it's easier to read and understand.
-- edit: BTW, just looked a little closer, and the 'easy-read' variation contains one char->date conversion + one concatenation,
but the other variant contains two int->date conversions (they are both implicit)...
/Kenneth

The difference will come out if the you have a large result set.

Hope you don't get upset with me, I just find these kind of discussions interesting. =)
If I'm to believe what's written in BOL, I doesn't seem to work quite that way.
I know how a datetime is stored, and perhaps there are some teeny advantage when it comes to ints, I can't tell. We have to ask the guy who wrote it.
Anyways, if we look at the expression (this may also serve to explain how this works to the one who asked)
with the function
DATEDIFF ( datepart , startdate , enddate )
According to BOL, startdate -
Is the starting date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Well, an integer zero isn't a datetime value, nor is it a string in a date format.
The only way I know of to get an int to become a datetime typed value is to CAST( 0 as DATETIME)
Since as duly noted, the datetime type is actually just numbers, zero is the value of the base-date - 1900-01-01
My reasoning here is that if startdate is supposed to be a datetype, and we feed it an int, then a conversion must happen somewhere.
DATEDIFF itself then retuns an int, which leads us to..

DATEADD (datepart , number, date )
DATEADD has the same typing for date parameter, but here we feed it with a -1
So, as I understand it, for -1 to become a date or a string in dateformat, some conversion has to happen here as well.
Again, I hope noone gets upset with me, I just enjoy debating details and particulars from time to time.
It doesn't mean I'm right, it's just how I view things.

On the subject of 'fastest' or 'slowest', I really think that the differences are minimal.
It's perfectly possible that the 'int-way' may use less cpu-cycles or similar, an int is certainly half the size of a string-date in ISO format.

With some very simple and unscientific testruns on my desktop, I rendered a million rows result in textpane in Management studio,
both using GETDATE() and a million row table with a clustered date column holding actual dates.
As expected when timing rendering to screen, there are some variations in time. Though when repeating a fes times and resources had settled somewhat,
the 'winner' on execution time was the 'int-date' version. (cheers Frank)
The difference however, for a million rendered rows, was around 0.5 seconds. (18423ms vs 18903ms)

=;o)
/Kenneth

So you consider 1M rows a large resultset?[|-)][]

You could always try my function.. but on that size dataset it may have issues too
http://www.sqlmag.com/Articles/ArticleID/97590/97590.html?Ad=1
It can also help you to find the first working day of every month or the first non working day or the first holiday or the 5th working day of next quarter. etc. Let me know if you have questions!

<P mce_keep="true">[quote user="Adriaan"] <P>So you consider 1M rows a large resultset?<IMG alt=Sleep src="http://sql-server-performance.com/Community/emoticons/emotion-20.gif"><IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P><P>[/quote]</P><P>If you're going to slap in someone's face, yes. []<BR>If it's not going into someone's face, then it's a batch, and a few seconds here or there aren't usually the same issue.<BR><BR>However, when someone asks to 'retrieve', it's ususally mean 'slap in the face result'.</P><P>=;o)<BR>/Kenneth</P>

Frank, that's what starts happening Sunday night, around 11.
It's the Twilight Zone - Ee-ah-ee-ah ee-ah-ee-ah ...[6]

Great job Frank, but how does this one work?