December 31, 2007 at 10:43 am
Concur... trips through CHAR/VARCHAR conversions are somewhat slower especially over millions of rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2007 at 11:09 am
Michael
Your code produces correct results, but I have found in testing that doing this by converting to strings and back to datetime is a much slower method that using the nested dateadd/datediff method that I posted.
Thanks for taking an interest in my version of the solution. It sounds as if I don't get the job, though. I'd be interested in the details of your timings.
I'm not sure that there should be a hard-and-fast rule as to which is the best technique. Possibly, when faced with the problem of having to do this particular calculation over millions of rows, I might want the Datead/Datediff method if it is really so much faster, but there seems little point in going for such small gains in code-size/performance in normal usage, where one is probably only doing the calculation once at the start of a routine. I just find the 'string-conversion' technique easier to understand, but I wouldn't want to criticise any other approach.
Best wishes,
Phil Factor
December 31, 2007 at 2:01 pm
You are correct... makes no significant difference if you only use it once at the beginning of a sproc... unless the sproc is being called millions of times a day for a GUI.
Or, use it on a million rows and it makes 10 seconds difference... maybe trivial to most who have to process a million rows, but that'll bring us to my second point...
The "hard-and-fast" rule that I use is that it should always be "fast" because you never know when scalability will increase, sometimes, dramatically.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2008 at 2:58 am
I tried the two techniques on a table of a million rows. Michael and Jeff are correct in that the DateAdd/DateDiff routine is a lot faster, but even on my clunky test system it still takes only 3 seconds as opposed to 0.6 seconds to process a million rows.
Here is the test code. The Tester table was simply a primary key, two date fields and a comment field, all populated with SQL Data Generator (the Beta is a free download from Red-Gate's Red-Gate Developer Forum)
[font="Courier New"]
--1) How to find out the current month's first Date ?
SET STATISTICS TIME ON
SELECT COUNT(*) FROM tester WHERE
datelogged>CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DateAssessed,113),8))--3203 ms
SELECT COUNT(*) FROM tester WHERE
datelogged>DATEADD(MONTH,DATEDIFF(MONTH,0,DateAssessed),0)--607 ms
--2) How to find out the current month's Last Date ?
SELECT COUNT(*) FROM tester WHERE
datelogged>DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,DateAssessed),113),8))--3221 ms
SELECT COUNT(*) FROM tester WHERE
datelogged>DATEADD(MONTH,DATEDIFF(MONTH,-1,DateAssessed),-1)--605 ms[/font]
... the other examples had very similar timings
Best wishes,
Phil Factor
January 1, 2008 at 8:18 am
Still, ya gotta admit, Phil... that's 5 times faster... if you make those types of small improvements in many areas of a whole batch that makes multiple passes on a million rows (for whatever reason), those small improvements really start to add up. And, for GUI return code... 3 seconds would not be acceptable where .6 seconds might be.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2008 at 3:04 am
Just had to laugh at this question 😉
I applaud your answers though, some of the questions in the forum seem just too 'homeworky' in nature.
--Shaun McGuile
Hiding under a desk from SSIS Implemenation Work :crazy:
January 2, 2008 at 3:30 am
Heh... considering that the OP said...
My friend faced the above questions in his recent interview.
... I'd have to agree 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2008 at 4:16 am
Yeah Jeff - I'm just so cynical these days.
Though to be fair in an interview I would not expect anyone to know the exact syntax required to address the question just the root principles of using GetDate(), and being able to manipulate the result with dateadd and datediff.
I have written the code to do this in every programming environment/language I have used in the past 20 years.
Its kind of 'bread and butter' type basic programming. I just think if anyone can't come up with a working solution within a reasonable amount of time having all of the tools available to them then they should switch careers to something else as software development is not for them. (Unless they are a neophyte/student - then its hit the books and experiment).
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 2, 2008 at 10:24 am
I find that the dateadd/datediff method is the most convenient way to code for these sorts for problems. Once you understand what it is doing, it is very intuitive and easy to remember. Most of the functions on these links use some variation of dateadd/datediff.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
End Date of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
End of Week Function:
January 2, 2008 at 11:46 am
For the beginning and end of the day, week, month, quarter, year...
http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/[/url]
I've done the end of the intervals at 3ms before the beginning of the next (ie, end of today would be 2008/01/02 23:59:59.997) so that these can be accurately used in between statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 12:27 pm
GilaMonster (1/2/2008)
For the beginning and end of the day, week, month, quarter, year...http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/[/url]
I've done the end of the intervals at 3ms before the beginning of the next (ie, end of today would be 2008/01/02 23:59:59.997) so that these can be accurately used in between statements.
I prefer to write date queries in the form of greater than or equal to a start date time, and less than an end date time. I use this approach because it is more theoretically correct; time is not a series of discrete points, it is a range. “Today” is really any time value that is on or after 00:00:00.000 today and before tomorrow at 00:00:00.000; there is no actual last time for today. This approach is also immune to possible changes in the precision of the date time data type in future releases. The technique of using between 00:00:00.000 and 23:59:59.997 is only valid if the time precision is the current 3 milliseconds. If it changed to a millionth of a second, there would be a gap at the end the time range.
For example, to find all values for today:
select
*
from
MyTable
where
-- Greater than or equal to today
MyDateColumn >= dateadd(day,datediff(day,0,getdate()),0) and
-- Less than tomorrow
MyDateColumn < dateadd(day,datediff(day,0,getdate())+1,0)
January 2, 2008 at 12:32 pm
Michael Valentine Jones (1/2/2008)
I prefer to write date queries in the form of greater than or equal to a start date time, and less than an end date time.
Each to their own. *shrug* 😉
I'll probably rewrite these when SQL 2008 comes out, to take advantage of the datetime2 and date datatypes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 8:24 pm
Michael Valentine Jones (1/2/2008)
I prefer to write date queries in the form of greater than or equal to a start date time, and less than an end date time. I use this approach because it is more theoretically correct; time is not a series of discrete points, it is a range. “Today” is really any time value that is on or after 00:00:00.000 today and before tomorrow at 00:00:00.000; there is no actual last time for today. This approach is also immune to possible changes in the precision of the date time data type in future releases. The technique of using between 00:00:00.000 and 23:59:59.997 is only valid if the time precision is the current 3 milliseconds. If it changed to a millionth of a second, there would be a gap at the end the time range.
Heh... for the same reasons, I do the very same thing. In fact, I incorporated that same method into the SQL Standards I wrote for work because so many of the developers thought that subtracting 1ms would do the trick...
I do believe, though, that the resolution actually pans out to 3.3 milliseconds, but I could be wrong and it certainly won't matter using th whole date method. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2008 at 8:55 pm
Sanjay Pandey (12/21/2007)
Using Datefunctions.select getdate()-day(getdate()) as LastDayLastMth, getdate()-(day(getdate())-1) as FirstDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate())) as LastDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate()))+1 as FirstDayNextMth
Friends, please analyse the above one also in comparision to datediff. I use it always and would be interested in knowing, whether this is ok or I should switch to datediff.
To me it looked like using plain subtraction, instead of calling another function is faster.
Comments please.
January 2, 2008 at 10:53 pm
Shaun McGuile (1/2/2008)
Yeah Jeff - I'm just so cynical these days.Though to be fair in an interview I would not expect anyone to know the exact syntax required to address the question just the root principles of using GetDate(), and being able to manipulate the result with dateadd and datediff.
I have written the code to do this in every programming environment/language I have used in the past 20 years.
Its kind of 'bread and butter' type basic programming. I just think if anyone can't come up with a working solution within a reasonable amount of time having all of the tools available to them then they should switch careers to something else as software development is not for them. (Unless they are a neophyte/student - then its hit the books and experiment).
--Shaun
Heh... spot on, Shaun!!! But you think you're cynical? After I've had folks come in for an interview claiming to be a "9 out of 10 in SQL Server" and they can't tell me which function returns the current date and time? And, they're applying for the position of "Senior SQL Developer"? :sick:
I believe that if they're applying for such an important position on my databases, they should be able to write the code for those simple formulas with the letters from the alpha-bet soup they had for lunch! 😛 These are not difficult formulas to remember if you've actually used them more than a couple of times. Certainly, you should be able to correctly science out the formula on a piece of paper in less than 60 seconds each.
I believe that a good strong mathematical background can pretty much guarantee that you can learn anything you need to about SQL and, that if you can show me a couple of practical things in mathematics, I'll give you a shot. So, how come I've interviewed (count 'em!) 5 folks who supposedly have Masters degrees in Mathematics and one with a PHD that couldn't tell me the decimal equivalents for 1416, 20, or even 23????
Same thing goes with Computer Science majors... If they can even show me a glimmer of intelligence, I'll give 'em a chance... Master's in CS couldn't tell me what would happen to bit 3 of a byte if I multiplied the byte by 2. Reaching way back for a simple question, I asked him what "CPU" stood for... and he didn't know!
I believe that if you claim to be a "Senior Java Developer" with "5 to 8 years experience", that you should be able to tell me how to keep a web user from saving the same record more than once by hitting the SAVE button more than once during the same session. I've had people tell me they would rollback the current transaction and save the latest one. I've had people tell me they would put in a 5 second delay before the save actually started. I've had people tell me they would dip the DB to see if the row already existed. Only two out of 8 of them gave me the simple and correct answer.
I agree with you 100%... basic "bread'n'butter" knowledge of just about anything is missing from a good number of people's backgrounds that are applying for some pretty important positions. I don't know who ends up hiring the folks I reject at other companies, but I pity those poor souls... they have no idea the pain they've brought aboard. :Whistling:
Sorry folks... bit of a rant on my part... I'm just a bit disgusted with the breadth of the ID-TEN-T's I've had the great misfortune of interviewing. Where do these people come from? Don't even get me started about spelling errors on a resume that's supposedly been reviewed and approved by the local body shop...:hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply