T-SQL query
-
Comments posted to this topic are about the item T-SQL query
-
Very tricky! I made a guess. Yes. Anything is possible.
-
you could use a case statement:
where
case month(dob)
when 1 then 1
when 3 then 1
when 4 then 1
when 5 then 1
when 7 then 1
when 8 then 1
else 0 end = 1
Life: it twists and turns like a twisty turny thing
-
Handy to know...I'm sure I'll be using that in my code very soon!
-
That was a very nice question for a Friday morning.
Another solution would be:
datepart(m,dob) between 1 and 8
and datepart(m,dob) <> 2
and datepart(m,dob) <> 6
Though the single condition definitely beats this solution.
-
Knut Boehnert (5/2/2008)
That was a very nice question for a Friday morning.I'd have to disagree. I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. I decided to be contrary and guessed no, based on the last (equally pointless) one of these being 'yes'.
-- Kev
-------------------------------Oh no!
-
Kevin Gill (5/2/2008)
Knut Boehnert (5/2/2008)
That was a very nice question for a Friday morning.I'd have to disagree. I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. I decided to be contrary and guessed no, based on the last (equally pointless) one of these being 'yes'.
-- Kev
Kevin all solutions may not be the best but sometimes these type of solutions/questions shake our brains. We sometimes do monotonous work, so this solutions help us to break the shackle. We learnt something differently is also interesting. 😎
-
You can use UNION...
good one...;)
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Since the question clearly states:
The condition should not use both IN and OR operators.
use could use:
where DatePart(m, ) IN (1,3,4,5,7,8)
OR is not used, so since they BOTH aren't used, this is valid.
you also could use:
where DatePart(m, date) = 1
or DatePart(m, date) = 3 ....
as long as you don't use IN, this also would satisify the requirement not to use both.
The question should have stated
The condition should not use either the IN and OR operators.
Yes, I know what was meant. But the requirements didn't adequately specify the intention.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (5/2/2008)
Since the question clearly states:The condition should not use both IN and OR operators.
use could use:
where DatePart(m, ) IN (1,3,4,5,7,8)
OR is not used, so since they BOTH aren't used, this is valid.
Exactly the answer I was about to give. 🙂 I figured the question had to have been misworded, it's a complete no-brainer as is.
Ron Moses
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
As I already stated in one of the previous QODs with a similar question:
The suggested answer does not work with languages other than english...
Try this instead:
WHERE '1;3;4;5;7;8' LIKE '%' + CAST(MONTH(dob) as varchar(2)) + '%'Best Regards,
Chris Büttner
-
where '_'+ convert(varchar, datepart(d, dob) ) + '_' like '%_1_3_4_5_7_8_%'
What about this ?
-
Lots of different solutions.
The only one that won't always work is the answer given.
As a reader based in continental europe, I should point out the fact that if you're running SQL server in any language other than English, then you'll almost certainly get a different result for datepart(m,dob) like '%e%'
Try changing the language to Italian, and see how your code runs 😉
SET LANGUAGE Italian
-
I just figured you would have a months table with the number of days in the month as each of the months mentioned have 31 days. So I would have
Where days_in_month = 31 and month(dob) < 9.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Umm, it may be that my brain is fuzzy on a Fri morning, but isn't Select datename(m,'2008-07-12') = 'July' (in English) which I'm pretty sure doesn't have an "e" in it, yet 7 is one of the months listed.
I didn't get it right, however, many people have proposed solutions that *do* work without using OR or IN, but as written the QoD answer is incorrect. (in English, it'd have even more limitations in other languages).
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply