SQL Clone
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 Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8505 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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5717 Visits: 2096
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
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103483 Visits: 15046
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
Dont 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 QuestionHow 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
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5717 Visits: 2096
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
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 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
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16754 Visits: 4141
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 says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103483 Visits: 15046
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
Consultant Straight Path Solutions
Dont 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 QuestionHow 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