December 30, 2015 at 9:28 pm
Comments posted to this topic are about the item It's the end of the year
December 31, 2015 at 12:44 am
The correct answer is commented out so would return nothing. Are we to guess that the first select is commented and the second is not? Two poor questions to end the year.
Fitz
December 31, 2015 at 1:03 am
Lets not make another issue here guys. C'mon ... Its the End of the year 😛 Celebrate it.
Keep up the good works and Thanks for 7 points Steve 🙂
Happy New Years to all. Have a good healthy 2016 with any Year End / Month End Production related issues.
December 31, 2015 at 1:59 am
Excellent question for the last day of the year especially with an awesome trick : the comment for the 1st select in the 4th choice.
Before posting here , I have searched on Internet as I was sure to have followed a thread about the same topic long time ago , when I was beginning to reply on forums. It is a complicated solution but , what do you think of this link and especially the Adam Haines's code ?
And happy new year for everybody...
December 31, 2015 at 2:51 am
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*
So let me try to offset that by contributing something that perhaps might actually be useful for some people.
The DATEADD / DATEDIFF year trick used in all of the answer options is in fact a quite common trick to find the start of any period. Before the date data type was introduced, its most common application was to strip off the time part of a datetime: [font="System"]DATEADD(day, DATEDIFF(day, '20150101', DatetimeExpression), '20150101')[/font] will always return the day in the DatetimeExpression with the time part set to midnight. Change the timepart specification to week, month, year, etc to go to the start of the week, month, or year instead. The trick that makes this work is that the DATEDIFF expression finds the number of whole days since a reference date, then DATEADD adds that number to that same reference date.
As you see, I use an actual date for the reference date, instead of the integer value 0 which Steve used. Which date I choose is irrelevant, as long as it's the same in both places. There are a few good reasons for doing this:
1. Maybe not the most relevant, but purity matters to me. I dislike the implicit conversion from int to datetime. I am not even sure if it is officially documented or supported (though I do not expect it to stop working anytime soon - for the "older" date/time data types; for the "new" types this conversion actually does not work anymore!)
2. When you want to use the same trick to strip fractions of a second, you actually need to use something else. The value 0 implicitly converts to Jan 1st, 1900; and the number of seconds that has passed in 115 years will overflow the interger result of DATEDIFF. By using a reference date in the 21st century, you are safe from that. (Note that the reference date can be both before or after the date in DateExpression; the trick works either way)
3. You can actually do more complex things with this trick by using two different reference dates. True, the same works with integer (or even fractional) values as well, but it's often easier to understand what is going on if you use date values.
Some examples of more complex usages of the pattern include:
SELECT DATEADD(day, DATEDIFF(day, '20150101', CURRENT_TIMESTAMP), '20150101 12:00')
Returns noon of the current day. Same DATEDIFF expression, but that number of days is now added to noon of the reference date.
SELECT DATEADD(month, DATEDIFF(month, '20150101', CURRENT_TIMESTAMP), '20141231');
Returns the last day of the previous month (who needs EOMONTH anyway, eh?). Again, same DATEDIFF calculation, but now added to the day before that, to find the day before the start of the current month. Note that this will not work 100% correct if the second reference date is the last day of a month with less than 31 days.
SELECT DATEADD(year, DATEDIFF(month, '20000701', CURRENT_TIMESTAMP) / 12, '20000701');
Finds the start of Microsoft's fiscal year. I cannot use the year parameter for the DATEDIFF since this computes year boundaries, not years duration (so it would increase by one after Dec 31 anyway). Instead I use months and divide by 12; the integer division truncates the reminder. I picked a reference date in the past because integer division always truncates, which means it changes from round down to round up if the value is negative. Another way to prevent this would have been to use the FLOOR function.
SELECT DATEADD(month, DATEDIFF(month, '20030101', DATEADD(day, -2, CURRENT_TIMESTAMP)), '20030101');
Start of current bookkepping month for a system that always closes out the month after the second day of the next month.
SELECT DATEADD(day, (DATEDIFF(day, '20100103', CURRENT_TIMESTAMP) / 7) * 7, '20100103');
Last Sunday, without any dependency on the SET DATEFIRST or other locale settings. For this to work, the reference date must be a Sunday. (And as with the Microsoft bookyear example, it has to be in the past).
These are just a few examples, you can often find answers to datetime-related problems by using combinations of the above techniques.
And finally, the real correct answer to today's QotD is of course the following, much simpler expression:
SELECT DATEADD(year, DATEDIFF(year, '20100101', CURRENT_TIMESTAMP), '20101231');
December 31, 2015 at 3:38 am
Happy new year!
😎
December 31, 2015 at 3:59 am
Hugo Kornelis (12/31/2015) So let me try to offset that by contributing something that perhaps might actually be useful for some people.
Thanks very much for these examples Hugo, very useful, I've copied them to my sql examples file as I probably will have forgotten them by tomorrow morning.
SELECT 'Happy', 'New', SUBSTRING('DATEADD(DAY, -1, DATEADD(month, 12, DATEADD( Year, DATEDIFF(YEAR, 0, getdate()), 0 )))',46,4),'!'
December 31, 2015 at 7:18 am
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*
All very well for you to moan, Hugo, but inaccuracies do need to be pointed out (and corrected).
And in case you think everyone should have got it right, how is anyone to know that the 365 in the second option wasn't a typing error for 364 (adjacent key errors are common, aren't they) - seems a more likely typing error than typing "-- " instead of typing nothing, doesn't it?
I tend to agree that using 0 in place of some explicit date like '19000101' just makes the thing harder for a novice to understand, but I don't see why an implicit conversion from CHAR(8) to DATETIME is any better than an implicit conversion from INT to DATETIME except for the improved clarity for beginners. Anyway, if it's the implicit conversion you don't like presumably you think the code would be improved by writing "CAST(0 AS DATETIME)" instead of "0", and that strikes me as quite bizarre.
Tom
December 31, 2015 at 8:38 am
TomThomson (12/31/2015)
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*All very well for you to moan, Hugo, but inaccuracies do need to be pointed out (and corrected).
True, absolutely.
But I'd say that it suffices to say it once. Not twenty-five times (not making up that number, I actually just counted the number of messages about the mistake in yesterday's QotD and it's currently at twenty-five - and probably will go up further when people sober up after the holidays).
Too many people just pop in the comment without seeing that it's already been said, or they see that it's been said and still want to weigh in. Which is okay if you actually have anything usefull to add, but a waste of anyone's time if you're just repeating what has already been written.
I know that I cannot change it, but approximately once per year I just need to get it off my chest. You can all relax now, I'll calm down again. 🙂
December 31, 2015 at 9:43 am
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*
But the world will come to an end if I don't get my points!!!!!! I NEED MY POINTS!!!!!!
/sarc
December 31, 2015 at 9:48 am
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*
I feel you there Hugo!
Hugo Kornelis (12/31/2015)
1. Maybe not the most relevant, but purity matters to me. I dislike the implicit conversion from int to datetime.
I know that I'm being unnecessarily picky, but I'd like to point out that while your example may not have an implicit conversion from int to datetime, but it does have an implicit conversion from varchar to datetime since it referenced '20150101' twice. I'm assuming that in actual use you would assign the value to a datetime variable then reference that variable in your code to reduce the number of implicit conversions?
In any case, good explanation of the method used!
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
December 31, 2015 at 11:00 am
Aaron N. Cutshall (12/31/2015)
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*I feel you there Hugo!
Hugo Kornelis (12/31/2015)
1. Maybe not the most relevant, but purity matters to me. I dislike the implicit conversion from int to datetime.I know that I'm being unnecessarily picky, but I'd like to point out that while your example may not have an implicit conversion from int to datetime, but it does have an implicit conversion from varchar to datetime since it referenced '20150101' twice. I'm assuming that in actual use you would assign the value to a datetime variable then reference that variable in your code to reduce the number of implicit conversions?
In any case, good explanation of the method used!
Are you sure about that implicit conversion?;-):hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 31, 2015 at 1:37 pm
Aaron N. Cutshall (12/31/2015)
Hugo Kornelis (12/31/2015)
Oh boy. I guess we can all brace ourselves for another day where dozens of people feel that they just have to respond to inform the world that there is a typo in the answer and that they are robbed of a point. *sigh*I feel you there Hugo!
Hugo Kornelis (12/31/2015)
1. Maybe not the most relevant, but purity matters to me. I dislike the implicit conversion from int to datetime.I know that I'm being unnecessarily picky, but I'd like to point out that while your example may not have an implicit conversion from int to datetime, but it does have an implicit conversion from varchar to datetime since it referenced '20150101' twice. I'm assuming that in actual use you would assign the value to a datetime variable then reference that variable in your code to reduce the number of implicit conversions?
In any case, good explanation of the method used!
No, the conversion from string to datetime is actually documented. And there is no way to write a datetime constant other than as string (or int, but let's not go there again).
I must admit that I was sloppy in one case, namely the noon datetime constant. The officially supported and "locale-safe" formats are yyyymmdd for date only, and yyyy-mm-ddThh:mm:ss (optionally followed by a period and fractional seconds) for date+time. The format I used (yymmdd hh:mm) mostly works, but is not guaranteed. That was a mistake.
December 31, 2015 at 1:51 pm
Hugo Kornelis (12/31/2015)
No, the conversion from string to datetime is actually documented. And there is no way to write a datetime constant other than as string (or int, but let's not go there again).
I'm curious about that. While I realize that some type of conversion MUST occur, is it the same as typical implicit conversions? Jason Brimhall alluded to that in his earlier post. Where is this documented? I tried to look for it but kept getting general information about implicit conversions and one of Iztak Ben-Gan's books where he cautioned on date/time formats with implicit conversions. Previously I was just being pedantic, but now I'm truly curious.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
December 31, 2015 at 5:07 pm
Aaron N. Cutshall (12/31/2015)
Hugo Kornelis (12/31/2015)
No, the conversion from string to datetime is actually documented. And there is no way to write a datetime constant other than as string (or int, but let's not go there again).I'm curious about that. While I realize that some type of conversion MUST occur, is it the same as typical implicit conversions? Jason Brimhall alluded to that in his earlier post. Where is this documented? I tried to look for it but kept getting general information about implicit conversions and one of Iztak Ben-Gan's books where he cautioned on date/time formats with implicit conversions. Previously I was just being pedantic, but now I'm truly curious.
You raise a very interesting question, Aaron. First, let me expand a bit on implicit vs explicit conversions. The main problemswith implicit conversions is the lack of control. This can severely impact query performance if the conversion is done in a different direction than you expect. So in any case where an implicit conversion can affect query performance, I would avoid it. The second problem caused by lack of control is that SQL Server decides the resulting data type. If you do not want 12.3 to be typed as decimal(3,1), then you'll have to replace it with CAST(12.3 AS decimal(5,2)). That last example also affects date and time strings - using CONVERT with a style parameter allows you to tell SQL Server what date format is used; using implicit conversion (or CAST) reverts to default formats.
The second reason your question is interesting is that I have been convinced that this is documented for so long that I have not looked for it in a very long time. So I had to search for it, and I was surprised by the results. There is documentation - you can find it here: https://msdn.microsoft.com/en-us/library/ms187819.aspx (and you can use the links on the left side to look at how the rules change for data types other than datetime, e.g. datetime2 or date - they have interesting subtle differences.
The text could have been clearer, so here is some explanation. The first set of numeric formats explicitly mentions that the interpretation depends on language and SET DATEFORMAT setting. The second set (alphabetical) states that SET DATEFORMAT is not applied, but it also mentions (almost in passing) that the month name is interpreted based on language setting. (What this overview fails to mention, though, is how a date such as "15 Apr 12" would be interpreted if SET DATEFORMAT is not used; it can be either Apr 15, 2012; or Apr 12, 2015).
The third block lists the ISO format, which is independent of language and dateformat setting. What surprised me is to see that this block also lists YYYYMMDD hh:mm:ss - I always thought that this format was not guaranteed to be safe. The text in the description and the repetition of the same format in the fourth ("unseperated") block suggests that this is an error, though. And the lack of text in the description of the fourth block makes me wonder how well this has been proof-read.
The ODBC block, finally, lists another supported and safe format that is however hardly ever used. Since it is interpreted by the provider and not by SQL Server itself, I would avoid this - if you ever need to run through a provider that does not support it, you'll get errors.
I'll leave it as an exercise to the reader to compare all supported formats for all data types and try to extract a single format that always works and never depends on any settings; my bet is that the result will still be that you can only rely on either yyyymmdd or yyyy-mm-ddThh:m:ss. (But let me kniow if you find that this has actually changed!)
[Final note: yyyy-mm-dd is also an official ISO standard and supported for the newer data types, but the old data types for legacy reasons still support other formats that look the same, so implicit conversion based on yyyy-mm-dd is not 100% safe]
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply