My New Resolution
-
Comments posted to this topic are about the item My New Resolution
-
Heh.. I get an "Error: This question has no body." when I click on the link in the post above. Was the resolution to not post any more QOD's? 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, there's usually not much real body to New Year's resolutions...
Just because you're right doesn't mean everybody else is wrong. -
I guess Steve has no new years resolutions
-
Ha, I have a few, but mostly I don't know what happened. Apparently the question text was lost.
Updated now.
-
Easy enough. But I would personally assign whoever created that table with DATETIME instead of DATE to toilet cleaing duty for a week...
Just because you're right doesn't mean everybody else is wrong. -
It's the real world. They probably should clean toilets for a week, but we'd still be stuck with the table.
- Rune Bivrin wrote:
Easy enough. But I would personally assign whoever created that table with DATETIME instead of DATE to toilet cleaing duty for a week...
"It Depends". I can see someone designing this and wanting the time of day as a "yeah ,right" indicator for drunk/hangover conditions.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But the question was ONLY the year, and the year function (getdate()) doesn't do that?
- leonardo.ssantos79 wrote:
But the question was ONLY the year, and the year function (getdate()) doesn't do that?
It does, but there are two problems with that:
- It's not among the given alternatives
- You'd have to apply the YEAR() function to both the GETDATE() function and the column in the database. And that creates a non-SARG:able condition, i.e. one where an index on the column is rendered largely useless. NEVER apply a function or other expression to the column if there is another way to achieve the same actual condition by instead tweaking the search argument.
Just because you're right doesn't mean everybody else is wrong. - leonardo.ssantos79 wrote:
But the question was ONLY the year, and the year function (getdate()) doesn't do that?
The question was about how to find rows in a table based on the current year.
And temporal calculations can get a little weird because almost all of them (Access, Excel, Unix, Linux, whatever) operate on a base date and "Date Serial Numbers" behind the scenes.
The YEAR(GETDATE()) function combination returns a 4 digit NUMBER that represents the year. That number is NOT meant to be a date. If you do treat it like a date, it will be treated as the "date serial number". A date serial number in SQL Server is the number of days since 01 Jan 1900. If you try to convert that 4 digit number back to a date directly, the number 2022 will be converted to a date that's 2022 days after 01 Jan 1900.
SELECT YearFromDate = YEAR(GETDATE())
,DateFromYear = CONVERT(DATETIME,YEAR(GETDATE())) --2022 days after 01 Jan 1900
;I don't actually like this question because the given answer is totally dependent on the data. There is nothing in the table to prevent this table from having a time in the column which would make even the supposed correct answer for this problem totally incorrect.
In the same terms as the supposedly correct answer, you could do this and time wouldn't matter.
SELECT ResolutionText
FROM dbo.Resolution
WHERE ResolutionDate >= DATEADD(yy,DATEDIFF(yy, 0,GETDATE()),0)
AND ResolutionDate < DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),0)
;A slightly less arcane answer might come in the form of ...
SELECT ResolutionText
FROM dbo.Resolution
WHERE ResolutionDate >= DATEFROMPARTS(YEAR(GETDATE()) ,1,1)
AND ResolutionDate < DATEFROMPARTS(YEAR(GETDATE())+1,1,1)
;And, yes, I also agree with what Rune stated. YEAR(GEDATE() could be used on the right of the equation in the WHERE clause but YEAR(ResolutionDate) would have to be used on the left side and that make make it a Non-SARGable query which would guarantee a table scan instead of a seek or a seek following by a shorter range scan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply