Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL query


T-SQL query

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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
Bharat Shah-487869
Bharat Shah-487869
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
where '_'+ convert(varchar, datepart(d, dob) ) + '_' like '%_1_3_4_5_7_8_%'

What about this ?
David McKinney
David McKinney
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 2090
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 Wink

SET LANGUAGE Italian
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Sean Walker
Sean Walker
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 413
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).
David McKinney
David McKinney
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 2090
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 Smile You're brain is still a little fuzzy.
Sean Walker
Sean Walker
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 413
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.
Q -631159
Q -631159
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 165
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 have
Where days_in_month = 31 and month(dob) < 9.



Jack,

I believe April has 30 days in it.Wink

Q

Please take a number. Now serving emergency 1,203,894
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3035 Visits: 3755
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. :-)

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
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 have
Where days_in_month = 31 and month(dob) < 9.



Jack,

I believe April has 30 days in it.Wink


Oops. Guess I should be with Sean and at least avoid posting on Friday.BigGrin The great thing is a I got the answer right without even having a correct way of doing it.w00t



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search