May 2, 2008 at 6:19 am
Sean Walker (5/2/2008)
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).
always best to have a coffee before posting 🙂 You're brain is still a little fuzzy.
May 2, 2008 at 6:23 am
You're so right, feeling very silly right now. That's it, no QoD on Friday, my brain obviously has shut down from this week.
May 2, 2008 at 7:16 am
Jack Corbett (5/2/2008)
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 haveWhere days_in_month = 31 and month(dob) < 9.
Jack,
I believe April has 30 days in it.;)
Q
Please take a number. Now serving emergency 1,203,894
May 2, 2008 at 7:29 am
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.
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.
I agree. But I think the English phrasing should be: "The condition should not use either the IN or the OR operators." Or, alternatively, "The condition should use neither the IN nor the OR operators."
"The condition should not use either the IN and OR operators" correctly follows the computer logic needed (for example, something like condition_used <> 'IN' AND condition_used <> 'OR'). But in English "or" (or "nor") is used for this purpose.
Or maybe I'm using "or" too much. 🙂
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 2, 2008 at 7:41 am
Q (5/2/2008)
Jack Corbett (5/2/2008)
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 haveWhere days_in_month = 31 and month(dob) < 9.
Jack,
I believe April has 30 days in it.;)
Oops. Guess I should be with Sean and at least avoid posting on Friday.:D The great thing is a I got the answer right without even having a correct way of doing it.:w00t:
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
May 2, 2008 at 7:55 am
I'd never try to use some clever "all the months without 'e' in their name", so I just took the list as having some business-rule basis, and decided that, if I needed to implement it, I could easily use a CTE, derived table, or temp table, with those numbers in it, joined to the table being queried. No "in", no "or".
Yeah, the answer given is based on the silly "how many 2-cent stamps in a dozen", or "do they have the 4th of July in England", type process, but that doesn't make the answer I came up with technically inaccurate.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 7:59 am
In the "where" Clause the "datename" function is Nondeterministic function also when we use "like" operator then query can not use indexes on that key resulted in table scan rather than index seek.
I will prefer the Case Statement
i.e.
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
In the above statement month is used which is deterministic function and this query will take part in index seek.
Saim
May 2, 2008 at 8:06 am
At least some of you guys thought about it on a Friday morning. I have to admit all I did was look at the question and say yeah you can find a way to do it if you have the energy.
GSquared,
Can you still buy 2 cent stamps? That was even before my old bones! 😀
Q
Please take a number. Now serving emergency 1,203,894
May 2, 2008 at 8:45 am
I also used a simple case statement in the where clause. Although longer than the given answer, I prefer this method.
May 2, 2008 at 9:04 am
This solution assumes your database is in English, if it would be in Dutch the fifth month is ‘mei’ (has an e in it).
However ingenious this solution is I think it is rather farfetched and makes some assumptions.
May 2, 2008 at 9:19 am
Make very sure that you always add the T-SQL statement SET LANGUAGE ENGLISH before this request.
May 2, 2008 at 9:44 am
It's nice to see that I'm not the only one who is avoiding doing any real work this morning.
TGIF
May 2, 2008 at 9:54 am
Tom Garth (5/2/2008)
It's nice to see that I'm not the only one who is avoiding doing any real work this morning.TGIF
It is Friday and that is international professional development day, isn't it?
I know can we really call this professional development?
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
May 2, 2008 at 11:50 am
For some reason nobody mentioned as solutions:
- cursors
- visual basic application
- junior developer selecting records one by one
- Oracle administrator using his Oracle PL-SQL
- outputting all records to the flat file, printing them and let an intern use a pensil
and other convenient techniques
Regards,Yelena Varsha
May 2, 2008 at 12:01 pm
[not like '%e%'] will not work as it will include January and August also 🙂
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply