0

So given that every time I look for the SQL query to return the first day of the year I get this..Stackoverflow great answer

   SELECT
   DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1) AS EndOfYear

Is there anything wrong with using the below?

SELECT CONVERT(DATE,CONVERT(CHAR(4), Year(Getdate())) + '0101')

Personally I find this one a little easier to immediately understand.

Thanks!

Community
  • 1
  • 1
Dayton Brown
  • 1,228
  • 3
  • 16
  • 31
  • 1
    personally I don't see anything wrong with this. There are always a million ways to skin a cat. – Jeremy Jan 08 '16 at 14:39
  • Convert isn't ANSI, so you could argue that cast is better. That said in your example the convert expression is easier to read than the cast. This [SO question](http://stackoverflow.com/questions/707335/t-sql-cast-versus-convert) has a great discussion on just this topic. – David Rushton Jan 08 '16 at 14:53
  • The warehouse in question is on SQL Server. And there is no chance it will migrate to anything else. And if it did, using convert instead of cast would be the least of our worries. – Dayton Brown Jan 08 '16 at 17:06

3 Answers3

3

Depends what you mean by "wrong".

The other is probably faster because it keeps the dates in their native formats, which is actually numerical data in SQL Server, and SQL Server generally works faster with numbers than with strings. Plus there's the overhead of converting the date to a string and back.

But that performance difference is pretty minimal, so it depends on whether you'd rather have that extra bit of performance, or code that's easier to read and understand.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I'm not using these in any where clauses so the performance penalty would be unnoticeable. I'll probably end up using the date only functions, but I'm happy that I'm not going to cause any issues with my other method. – Dayton Brown Jan 08 '16 at 17:02
1

Nothing wrong at all, if you want to spend a few extra CPU cycles on processing strings.

I've seen great benefits from using a calendar table.
There are many examples on this, you can find one here.

Henrik Staun Poulsen
  • 13,154
  • 4
  • 23
  • 26
  • Funny enough, that would probably be the most readable version. And we have a calendar dimension table in the warehouse already. – Dayton Brown Jan 08 '16 at 17:11
0

Easier to understand??? Isn't that what comments are for?

In my opinion, the most important thing you do with a database is to make sure the data is accurate. The second most important thing is to make the code fast. As others have pointed out, the execution time difference is probably only a couple of computer cycles. The problem is... you could execute this on a table that has 100 million rows and suddenly those extra clock cycles takes several minutes.

I have no problem with people using "easy to understand" code when they don't know any better. In this case, you do know better. It would be a mistake to use the slower code. If you want to make it easy to understand, add comments to your code.

George Mastros
  • 24,112
  • 4
  • 51
  • 59