1

In the following T-SQL Code:

wt.clsdt_date >= CAST('1/1/' + CAST(2011 - 1 AS varchar) AS DateTime)

what does '1/1' mean?

Programming Newbie
  • 1,207
  • 5
  • 31
  • 51
  • 1
    While I appreciate all of the advice concerning the best way to write the above Code, I'm afraid I cannot do anything about how it is written, no matter how convoluted it may be. I have no access to the code to make the appropriate changes. I am merely trying to translate it into LINQ to SQL from a Word Document. However, your explanations have been very helpful in explaining exactly what is going on and for that I thank you all. – Programming Newbie Jun 08 '12 at 19:21

3 Answers3

3

(Please see the other answers for improvements to the SQL.)

'1/1/' is string so the resulting string after the + is '1/1/year', e.g.

   '1/1/' + CAST(2011 - 1 as varchar)
-> '1/1/' + '2010'
-> '1/1/2010'

which is then cast (converted, really) to the appropriate type.

I would be surprised if there is not a prettier method to do this.

For SQL Server 2008 and before there does not appear to be a particularly nice standard method.

For SQL Server 2012 there is DATEFROMPARTS as shown in this related SO post:

SELECT DATEFROMPARTS(@Year, @Month, @Day)
Community
  • 1
  • 1
3

A much safer way to do this is:

WHERE wt.clsdt_date >= CONVERT(CHAR(4), 2011 - 1) + '0101';

You don't need to explicitly cast it to a datetime, but you can:

WHERE wt.clsdt_date >= CONVERT(DATETIME, CONVERT(CHAR(4), 2011 - 1) + '0101');

This uses a non-regional date format and also still makes use of an index on the column, if one exists.

Also varchar without length is a bad habit.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • +1 Although '1/1/yyyy' should be "safe" in *this particular case*? I am not arguing against this approach, mind. –  Jun 08 '12 at 19:15
  • @pst the point is people might learn from the `1/1/` example and assume it is also safe for `6/15/` (which can yield an error) or even worse `6/7/` which could lead to wrong results but go undetected. – Aaron Bertrand Jun 08 '12 at 19:16
  • Yes, I agree entirely, this is a much better habit/way to write it. I was just wondering if it could affect this particular string input. –  Jun 08 '12 at 19:20
2

This technique is used to evaluate whether or not wt.clsdt_date is during or after the year 2010. The cleaner way to accomplish this would be as follows:

YEAR(wt.clsdt_date) >= 2010
George Aletras
  • 106
  • 5
  • 1
    That is not sargable. Wrapping the column with the `YEAR` function means an index can't be used. – Martin Smith Jun 08 '12 at 19:04
  • @MartinSmith Lame, I would expect the planner to be able to do some deductions there... –  Jun 08 '12 at 19:06
  • @pst - Mostly it doesn't even when in principle it could. [There is a request to extend the cases where it does here](http://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable) – Martin Smith Jun 08 '12 at 19:07
  • @MartinSmith - It appears to be closed – Lamak Jun 08 '12 at 19:09
  • 2
    @Lamak that doesn't mean that it won't be considered later if you vote and add comments. :-) Unfortunately "closed" in this context really means "closed at least for the current release." – Aaron Bertrand Jun 08 '12 at 19:10
  • @AaronBertrand I had already voted on that, and forgot about it till now. Too bad it won't be considered to this release – Lamak Jun 08 '12 at 19:12