Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Raghavan CV » T-SQL query
Add to briefcase
52 posts, Page 2 of 6
««12345»»»

T-SQL query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Christian Buettner-167247
Posted Friday, May 02, 2008 5:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 2,526, Visits: 3,620
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
Post #494143
Bharat Shah-487869
Posted Friday, May 02, 2008 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 04, 2008 11:50 PM
Points: 7, Visits: 7
where '_'+ convert(varchar, datepart(d, dob) ) + '_' like '%_1_3_4_5_7_8_%'

What about this ?
Post #494144
David McKinney
Posted Friday, May 02, 2008 6:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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
Post #494153
Jack Corbett
Posted Friday, May 02, 2008 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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

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
Post #494154
Sean Walker
Posted Friday, May 02, 2008 6:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 630, Visits: 282
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).
Post #494156
David McKinney
Posted Friday, May 02, 2008 6:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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.
Post #494158
Sean Walker
Posted Friday, May 02, 2008 6:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 630, Visits: 282
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.
Post #494161
Q -631159
Posted Friday, May 02, 2008 7:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, 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.;)

Q

Please take a number. Now serving emergency 1,203,894
Post #494219
webrunner
Posted Friday, May 02, 2008 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:10 PM
Points: 2,117, Visits: 2,211
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.

-------------------
"The chemistry must be respected." - Walter White

"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
Post #494228
Jack Corbett
Posted Friday, May 02, 2008 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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.;)


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.


Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
Post #494236
« Prev Topic | Next Topic »

Add to briefcase
52 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.