Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Date Puzzle Expand / Collapse
Author
Message
Posted Wednesday, October 27, 2010 1:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
GPO (10/27/2010)
spelling and gramer


Glad that did not go unoticed.
Post #1011839
Posted Wednesday, October 27, 2010 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
SanDroid (10/27/2010)
da-zero (10/27/2010)

Have smileys totally lost their value nowadays?


I am not certain. Are they?
I just used your response as a jumping off point.
I know you are not the one moderating the Question of the Day.
I did not mean it to be personal and thought you would realize that.

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 (sadly no smiley of a mime with a baguette)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1011858
Posted Wednesday, October 27, 2010 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 6,133, Visits: 8,399
SanDroid (10/27/2010)
Hugo Kornelis (10/27/2010)
[quote][b]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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011917
Posted Wednesday, October 27, 2010 3:50 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:05 PM
Points: 720, Visits: 696
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.
Post #1011925
Posted Wednesday, October 27, 2010 3:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Hugo Kornelis (10/27/2010)
[quote]SanDroid (10/27/2010)
[quote]Hugo Kornelis (10/27/2010)
[quote][b]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.
Post #1011926
Posted Wednesday, October 27, 2010 9:59 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 3,113, Visits: 11,543
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



Post #1012043
Posted Thursday, October 28, 2010 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
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.




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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1012089
Posted Thursday, October 28, 2010 9:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thanks for the question, though I agree with the other comments about the date string not working for all languages/settings.
Post #1012447
Posted Thursday, October 28, 2010 9:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1012449
Posted Thursday, October 28, 2010 12:00 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:05 PM
Points: 720, Visits: 696
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.
Post #1012551
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse