Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

T-SQL query Expand / Collapse
Author
Message
Posted Friday, May 2, 2008 5:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Posted Friday, May 2, 2008 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 4, 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
Posted Friday, May 2, 2008 6:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:19 AM
Points: 646, Visits: 1,854
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
Posted Friday, May 2, 2008 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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
Posted Friday, May 2, 2008 6:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:47 AM
Points: 630, Visits: 357
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
Posted Friday, May 2, 2008 6:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:19 AM
Points: 646, Visits: 1,854
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
Posted Friday, May 2, 2008 6:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:47 AM
Points: 630, Visits: 357
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
Posted Friday, May 2, 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
Posted Friday, May 2, 2008 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:46 PM
Points: 2,337, Visits: 2,675
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.


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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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 ««12345»»»

Permissions Expand / Collapse