SQL Server Performance

Query to get the last date of the previous month.

Discussion in 'SQL Server 2005 General Developer Questions' started by Varsha, May 22, 2008.

  1. Varsha New Member

    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?
  2. FrankKalis Moderator

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
  3. Kewin New Member

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

    [quote user="Kewin"]
    I just like to do stuff different than all others =;o)
    select dateadd(day, -1, convert(char(6), getdate(), 112) + '01')
    /Kenneth
    [/quote]
    It depends...
    ...as you know. [;)]
  5. pedrolopezmail New Member

    Frank Kalis version is faster. No varchar convertion.
  6. Varsha New Member

    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.


  7. FrankKalis Moderator

    [quote user="Varsha"]
    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]
    Just do a CONVERT(char(8), <expression>, 112)
  8. Kewin New Member

    [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
  9. Adriaan New Member

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

    [quote user="Kewin"]
    -- 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)...
    [/quote]
    Well, one thing to consider here is that the internal storage format of a datetime is 2 integers in a binary(8). Pull the first 4 bytes out and you have your int date value, do the int calculations on that value and recombine that with 0x00000000 to the binary(8) and thereafter do the fancy date math to display the value. I don't think there is much of a type conversion here really going on.



  11. Kewin New Member

    Hope you don't get upset with me, I just find these kind of discussions interesting. =:eek:)
    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
  12. Adriaan New Member

    So you consider 1M rows a large resultset?[|-)][;)]
  13. MichaelB Member

    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!
  14. Kewin New Member

    <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. [:p]<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>
  15. FrankKalis Moderator

    [quote user="Kewin"]
    Hope you don't get upset with me,
    [/quote]
    Definitely not. [;)]
    Me too.
    ...and it is likely that that guy doesn't work there anymore... [:D]
    You know, without actually seeing the code everything is nothing but guesswork. Sure, there is some sort for conversion taking place. However, the only conversion I would like to think that is taking place is the conversion from some compatible type to a datetime type. Once this is done, this datetime typed value is a binary(8) displayed as datetime in a user-friendly format. So, all calculations after that point are performed on the binary(8) type. Should actually be the same for string or int once that conversion has taken place.
    Yes, the difference in such tests is almost non existing. However, I think in the "big picture" in real world environments in which you don't run such isolated unit tests the difference might be "more" measurable. But then again, trying to tweak the last bit of performance by "tuning" such queries is something to be concerned about when everything else is running perfect which is almost never the case. I really think it is more important to stick to a solution that is both understandable and maintainable. And that depends....
    Cheers Kenneth and enjoy your weekend!
  16. FrankKalis Moderator

    Is it just me or does it also happen to other, that when you come back to a posting after some time you realize: "Man, unbelievable that I really wrote that". Sometimes I think of something and obviously write something completely different. All, I'm trying to say is that it is apparently faster to convert a number into its binary representation than to convert a string into a datetime typed value and from there into a number. Fewer steps to perform.
    Why is it sometimes so difficult to express oneself precisely? [:(]
  17. Adriaan New Member

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

    <p>[quote user="Adriaan"]</p><p>Frank, that's what starts happening Sunday night, around 11.</p><p>It's the Twilight Zone -&nbsp;Ee-ah-ee-ah ee-ah-ee-ah ...<img src="http://sql-server-performance.com/Community/emoticons/emotion-14.gif" alt="Devil"></p><p>[/quote]&nbsp;</p><p>There is much truth in this. [:D]&nbsp;</p>
  19. ABSINTHIST New Member

    Great job Frank, but how does this one work?

Share This Page