October 27, 2010 at 1:19 pm
GPO (10/27/2010)
spelling and gramer
😛
Glad that did not go unoticed. :hehe:
October 27, 2010 at 2:01 pm
SanDroid (10/27/2010)
da-zero (10/27/2010)
Have smileys totally lost their value nowadays?I am not certain. Are they? :w00t:
I just used your response as a jumping off point. 😛
I know you are not the one moderating the Question of the Day.:Wow:
I did not mean it to be personal and thought you would realize that. :sick:
My point was/is nobody realy moderates the QOTD, and that is taking focus away from good questions that promote learning and placing it on bad spelling and gramer.
Of course if it was not for all these Forum posts, how many people would have a status above rookie on this site?
BTW: What was the intermediary language between you native language and English as the 3rd language? Was it C++, COBOL, QBASIC, SQL? 😎
The funny thing is that I forgot a smiley in my response 🙂
Without it, the response seems quite harsh. When will the internet have intonation?
ps: my 2nd language is French :alien: (sadly no smiley of a mime with a baguette)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2010 at 3:32 pm
SanDroid (10/27/2010)
Hugo Kornelis (10/27/2010)
And if you ever install SQL Server on a German OS, acccepting all defaults, you'll find that the Server language also defaults to German..
Is that becuase of the Laguage of the OS, or the installing users regional settings in Control Panel?
Good question, but I can't answer it. All my SQL Server installations thus far have been on Dutch operating systems, and since there is no Dutch version of SQL Server, that means SQL Server defaults to US English settings.
When you responded to my inital post saying you disagreed that this was a good question and stated that the code was unsafe.
I never said that I disagree that this is a good question. I did say that I don't share your enthousiasm. I also said that the code was not safe outside US English.
It's not my habit to rate the questions here, except when I consider them to be exceptionally good or exceptionally bad. But you misunderstood me, ad maybe you're not the only one, so I'll set the record straight. In y opinion, this question is neither good, nor bad, but mediocre. The knowledge tested is so basic that I'd expect almost everyone to know it, and the answer options don't even include the distractors that might be believable (as pointed out by others in this thread). But on the positive side, there is ambiguity in the question text, and apart from the bad practice with the date format, there is no error in the code.
What was that supposed to make me or the author think?
Ich zweifele, er dachte, dass es ein Kompliment war.
Machine translation still isn't perfect. Correct German would be "Ich bezweifle, dass er dachte, es war ein Kompliment". But my English is much better than my German, so unless you're good at Dutch, I suggest we stick to English.
To answer your question - what my comment was supposed to make you think, is exactly what I wrote in my comment: that I don't share your enthousiasm, and that the date format used in not locale-safe.
And that concludes my final comments on this subset of the discussion, as this whole "who said what and who meant what when he said that" discussion adds nothing. I'm here to discuss the nuts and bolts of SQL Server.
October 27, 2010 at 3:50 pm
So, is this the simplest and quickest way to get the last day of any month?
DATEADD(MONTH,x,'2010-Jan-01')
Where x is the month you are looking for the last day.
I've seen a lot of code over the years that performs some sort of find the first day of the month after the month in question, then subtract 1 day. Seems totally unnecessary with this function.
October 27, 2010 at 3:53 pm
Hugo Kornelis (10/27/2010)
SanDroid (10/27/2010)
Hugo Kornelis (10/27/2010)
And if you ever install SQL Server on a German OS, acccepting all defaults, you'll find that the Server language also defaults to German..
that means SQL Server defaults to US English settings.
I'm here to discuss the nuts
You have done well in contradicting your own "facts" in your own posts about the nuts of SQL installs. Is the information in your other post is as correct.
FYI: I lived in Bad Kreuznach for five years. I have no need for machine translators to type German (not Dutch) poorly.
October 27, 2010 at 9:59 pm
Dan Guzman - Not the MVP (10/27/2010)
So, is this the simplest and quickest way to get the last day of any month?DATEADD(MONTH,x,'2010-Jan-01')
Where x is the month you are looking for the last day.
I've seen a lot of code over the years that performs some sort of find the first day of the month after the month in question, then subtract 1 day. Seems totally unnecessary with this function.
Not sure if you mis-typed that, but it is fairly obvious that will return the FIRST day of the month AFTER x. For example, if x=10, it will return 2010-11-01 00:00:00.000.
This is the standard and most efficient way of getting the last day of the month a particular date is in:
select LastDay = dateadd(mm,datediff(mm,-1,getdate()),-1)
Result:
LastDay
-----------------------
2010-10-31 00:00:00.000
October 28, 2010 at 12:18 am
SanDroid (10/27/2010)
You have done well in contradicting your own "facts" in your own posts about the nuts of SQL installs. Is the information in your other post is as correct.
:rolleyes:
German: there exists a German version of SQL Server --> default language is German
Dutch: there is no Dutch version of SQL Server --> default language is US English
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 28, 2010 at 9:55 am
Thanks for the question, though I agree with the other comments about the date string not working for all languages/settings.
October 28, 2010 at 9:55 am
Is that becuase of the Laguage of the OS, or the installing users regional settings in Control Panel?
That depends:
If you install SQL Server running under the SYSTEM (machine) account, it will pick up the OS's regional settings (so if your OS is German, the regional settings for the SYSTEM account are German ...).
If you install SQL Server running under a dedicated account, it will use the regional settings of that particular user/account.
Regards,
Michael
October 28, 2010 at 12:00 pm
Michael Valentine Jones (10/27/2010)
Dan Guzman - Not the MVP (10/27/2010)
So, is this the simplest and quickest way to get the last day of any month?DATEADD(MONTH,x,'2010-Jan-01')
Where x is the month you are looking for the last day.
I've seen a lot of code over the years that performs some sort of find the first day of the month after the month in question, then subtract 1 day. Seems totally unnecessary with this function.
Not sure if you mis-typed that, but it is fairly obvious that will return the FIRST day of the month AFTER x. For example, if x=10, it will return 2010-11-01 00:00:00.000.
This is the standard and most efficient way of getting the last day of the month a particular date is in:
select LastDay = dateadd(mm,datediff(mm,-1,getdate()),-1)Result:
LastDay-----------------------
2010-10-31 00:00:00.000
You are correct, I meant January 31st. so:
DATEADD(MONTH,x-1,'2010-Jan-31') so that x is the month your looking for,
or for last day of the current month:
DATEADD(MONTH,MONTH(GETDATE())-1,'2010-Jan-31')
I even think
DATEADD(MONTH,MONTH(GETDATE())-1,CAST(YEAR(GETDATE()) AS VARCHAR(4)) +'-Jan-31')
is easier to read than the one you mention, with the DATEDIFF, but that's just me.
October 28, 2010 at 12:37 pm
Dan Guzman - Not the MVP (10/28/2010)
Michael Valentine Jones (10/27/2010)
Dan Guzman - Not the MVP (10/27/2010)
So, is this the simplest and quickest way to get the last day of any month?DATEADD(MONTH,x,'2010-Jan-01')
Where x is the month you are looking for the last day.
I've seen a lot of code over the years that performs some sort of find the first day of the month after the month in question, then subtract 1 day. Seems totally unnecessary with this function.
Not sure if you mis-typed that, but it is fairly obvious that will return the FIRST day of the month AFTER x. For example, if x=10, it will return 2010-11-01 00:00:00.000.
This is the standard and most efficient way of getting the last day of the month a particular date is in:
select LastDay = dateadd(mm,datediff(mm,-1,getdate()),-1)Result:
LastDay-----------------------
2010-10-31 00:00:00.000
You are correct, I meant January 31st. so:
DATEADD(MONTH,x-1,'2010-Jan-31') so that x is the month your looking for,
or for last day of the current month:
DATEADD(MONTH,MONTH(GETDATE())-1,'2010-Jan-31')
I even think
DATEADD(MONTH,MONTH(GETDATE())-1,CAST(YEAR(GETDATE()) AS VARCHAR(4)) +'-Jan-31')
is easier to read than the one you mention, with the DATEDIFF, but that's just me.
I think it’s debatable that the code you posted is easier to read, especially since is much longer. Maybe what you really mean is that you find it easier to understand. I have to disagree, especially since the method I posted is so easy to adapt to similar problems:
select Description = 'Getdate()',DT = getdate()union all
select 'Start of Day',DT = dateadd(dd,datediff(dd, 0,getdate()), 0)union all
select 'Start of Yesterday',DT = dateadd(dd,datediff(dd, 0,getdate())-1, 0)union all
select 'Start of Next Day',DT = dateadd(dd,datediff(dd, 0,getdate())+1, 0)union all
select 'First Day of Month',DT = dateadd(mm,datediff(mm, 0,getdate()), 0)union all
select 'Last Day of Month',DT = dateadd(mm,datediff(mm,-1,getdate()),-1)union all
select 'First Day of Last Month',DT = dateadd(mm,datediff(mm, 0,getdate())-1, 0)union all
select 'Last Day of Last Month',DT = dateadd(mm,datediff(mm,-1,getdate())-1,-1)union all
select 'First Day of Next Month',DT = dateadd(mm,datediff(mm, 0,getdate())+1, 0)union all
select 'Last Day of Next Month',DT = dateadd(mm,datediff(mm,-1,getdate())+1,-1)union all
select 'First Day of Quarter',DT = dateadd(qq,datediff(qq, 0,getdate()), 0)union all
select 'Last Day of Quarter',DT = dateadd(qq,datediff(qq,-1,getdate()),-1)union all
select 'First Day of Year',DT = dateadd(yy,datediff(yy, 0,getdate()), 0)union all
select 'Last Day of Year',DT = dateadd(yy,datediff(yy,-1,getdate()),-1)union all
select 'First Day of Last Year',DT = dateadd(yy,datediff(yy, 0,getdate())-1, 0)union all
select 'Last Day of Last Year',DT = dateadd(yy,datediff(yy,-1,getdate())-1,-1)union all
select 'First Day of Next Year',DT = dateadd(yy,datediff(yy, 0,getdate())+1, 0)union all
select 'Last Day of Next Year',DT = dateadd(yy,datediff(yy,-1,getdate())+1,-1)
Results:
Description DT
----------------------- -----------------------
Getdate() 2010-10-28 14:27:12.620
Start of Day 2010-10-28 00:00:00.000
Start of Yesterday 2010-10-27 00:00:00.000
Start of Next Day 2010-10-29 00:00:00.000
First Day of Month 2010-10-01 00:00:00.000
Last Day of Month 2010-10-31 00:00:00.000
First Day of Last Month 2010-09-01 00:00:00.000
Last Day of Last Month 2010-09-30 00:00:00.000
First Day of Next Month 2010-11-01 00:00:00.000
Last Day of Next Month 2010-11-30 00:00:00.000
First Day of Quarter 2010-10-01 00:00:00.000
Last Day of Quarter 2010-12-31 00:00:00.000
First Day of Year 2010-01-01 00:00:00.000
Last Day of Year 2010-12-31 00:00:00.000
First Day of Last Year 2009-01-01 00:00:00.000
Last Day of Last Year 2009-12-31 00:00:00.000
First Day of Next Year 2011-01-01 00:00:00.000
Last Day of Next Year 2011-12-31 00:00:00.000
Extensive testing has shown that date manipulations involving conversion to character strings are much less efficient than the code I posted. Also, that code does not work with settings of language that are non-english, so at the very least, you should change the code to this:
select dateadd(month,month(getdate())-1,cast(year(getdate()) as varchar(4)) +'0131')
October 31, 2010 at 8:21 pm
Simple & Easy
November 2, 2010 at 10:22 am
Accurate question. I think any question that uses a date with obvious notation to remove all ambiguity such as '2010-Jan-01' is fine. We clearly understand which is the year, month, and day. '201000205' while most would consider it to be February 5, 2010; 5 February 2010; or 2010 Februrary 5 it isn't perfectly clear.
I would have liked to seen this instead:
DATEADD(MONTH, 1, '2010-Jan-30')
Feb 28 is correct not Feb 27 day preceeding end of month nor March 1 30 days post Jan 30.
Tim
November 2, 2010 at 10:31 am
timothy bates (11/2/2010)
Accurate question. I think any question that uses a date with obvious notation to remove all ambiguity such as '2010-Jan-01' is fine. We clearly understand which is the year, month, and day. '201000205' while most would consider it to be February 5, 2010; 5 February 2010; or 2010 Februrary 5 it isn't perfectly clear.I would have liked to seen this instead:
DATEADD(MONTH, 1, '2010-Jan-30')
Feb 28 is correct not Feb 27 day preceeding end of month nor March 1 30 days post Jan 30.
Tim
The problem with '2010-Jan-01' is that it is not even a valid date with many or most language settings.
set language italian;
select DT = convert(datetime,'2010-Jan-01')
Result:
L'impostazione della lingua è stata sostituita con Italiano.
Server: Msg 241, Level 16, State 1, Line 3
Conversione non riuscita durante la conversione di una stringa di caratteri nel tipo di dati datetime.
'201000205' is perfectly clear to SQL Server. It will only interpret it one way, as YYYYMMDD, no matter what the setting of LANGUAGE or DATEFORMAT.
November 2, 2010 at 10:55 am
Michael Valentine Jones (11/2/2010)
timothy bates (11/2/2010)
Accurate question. I think any question that uses a date with obvious notation to remove all ambiguity such as '2010-Jan-01' is fine. We clearly understand which is the year, month, and day. '201000205' while most would consider it to be February 5, 2010; 5 February 2010; or 2010 Februrary 5 it isn't perfectly clear.I would have liked to seen this instead:
DATEADD(MONTH, 1, '2010-Jan-30')
Feb 28 is correct not Feb 27 day preceeding end of month nor March 1 30 days post Jan 30.
Tim
The problem with '2010-Jan-01' is that it is not even a valid date with many or most language settings.
set language italian;select DT = convert(datetime,'2010-Jan-01')
Result:
L'impostazione della lingua è stata sostituita con Italiano.
Server: Msg 241, Level 16, State 1, Line 3
Conversione non riuscita durante la conversione di una stringa di caratteri nel tipo di dati datetime.
'201000205' is perfectly clear to SQL Server. It will only interpret it one way, as YYYYMMDD, no matter what the setting of LANGUAGE or DATEFORMAT.
Actually there is an error. One too many 0's between the 1 and second 2 (mine but I am getting picky now).
As long as the intent is clear and ambiguity can't be inferred I am fine with however it is posted. I don't execute QOTD questions to determine my answer. I work through it as though I am reviewing a printout of code or taking an exam. I assume (wrongly at times) that dates are shown to be compliant with the posters environment but may not be with mine. If someone posted a question with a date '2010-Juli-06'. I would assume it works with the local install. I haven't had to account for backup/failover servers installed in separate countries as part of a disaster recovery/business continuity plan (yet). Of course this could be a whole separate question.
Maybe all questions should include the Set Language command to remove all possible ambiguity or something like:
declare @dt as datetime
select @dt = <an acceptable date format for January 3, 2012>
select dateadd(mm,2,@dt) as two_months_later
Tim
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply