Solved

Query Problem With DatePart

Posted on 2008-10-13
3
211 Views
Last Modified: 2010-03-20
Hello,
I am attempting to filter my CaseNumber column (Text) by the first 2 digits by using the following SQL query and I keep getting an error.

SELECT     MAX(Mid(CaseNumber, 3, 6)) AS CaseValue
FROM         ServiceCalls
WHERE     (LEFT(CaseNumber, 2) = DatePart(yy, NOW()))
0
Comment
Question by:Gunit2507
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 500 total points
ID: 22707110
LEFT() returns a string. DATEPART() returns an integer. I guess the message you are getting says "implicit conversion..."

Furthermore, DATEPART(yy ... ) returns a 4-digit year, not a 2-digit year as you seem to think: year and yyyy and yy  all mean the same thing.

GROUP BY CONVERT(INT, LEFT(CaseNumber, 2))
HAVING      CONVERT(INT(LEFT(CaseNumber, 2)) = (DatePart(yy, NOW())) % 100)

0
 
LVL 5

Assisted Solution

by:PaulKeating
PaulKeating earned 500 total points
ID: 22707136
Unmatched parens: fix is

GROUP BY CONVERT(INT, LEFT(CaseNumber, 2))
HAVING      CONVERT(INT, LEFT(CaseNumber, 2)) = (DatePart(yy, NOW()) % 100)
0
 

Author Comment

by:Gunit2507
ID: 22707228
This seems to work:

SELECT     MAX(Mid(CaseNumber, 3, 6)) AS CaseValue
FROM         ServiceCalls
WHERE     (LEFT(CaseNumber, 2) = RIGHT(DatePart('yyyy', NOW()), 2))
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question