April 14, 2008 at 8:05 pm
Comments posted to this topic are about the item T-SQL Query
April 14, 2008 at 11:34 pm
The explanation is incorrect.
First of all the LENGTH of the string representation of the months is EVEN, not ODD.
Second of all, the proposed method is NOT THE ONLY ONE another way.
So the answer should not start from BECAUSE, it should start from BECAUSE FOR EXAMPLE....
Another (not the last) way to extract is to JOIN the table stud_mast ON month(dob) with another table of subselect, or UNION, which contains the given numbers : 2,6,7,8,11,12.
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
April 15, 2008 at 1:20 am
Clsoe but no cigar!
Yes, these are all the months with an even count of characters - all the other months have an odd number of characters.
April 15, 2008 at 1:54 am
There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right way:D
That's one of the attributes that separate a DBA from a developer!
April 15, 2008 at 2:46 am
select * where month = 2
union all
select * where ...
...
???
April 15, 2008 at 3:06 am
Hello,
The proposed solution doesn't work with other languages:
SET LANGUAGE GERMAN
SELECT Datename(m, TheDate)TheMonthName, TheDate, Month(TheDate) TheMonth FROM (
SELECT '19000101' AS TheDate
UNION ALL SELECT '19000201'
UNION ALL SELECT '19000301'
UNION ALL SELECT '19000401'
UNION ALL SELECT '19000501'
UNION ALL SELECT '19000601'
UNION ALL SELECT '19000701'
UNION ALL SELECT '19000801'
UNION ALL SELECT '19000901'
UNION ALL SELECT '19001001'
UNION ALL SELECT '19001101'
UNION ALL SELECT '19001201'
) TheDates
WHERE LEN(Datename(m, TheDate))%2 = 0
Changed language setting to Deutsch.
TheMonthName TheDate TheMonth
------------------------------ -------- -----------
Januar 19000101 1
März 19000301 3
Juni 19000601 6
Juli 19000701 7
August 19000801 8
November 19001101 11
Dezember 19001201 12
Another solution could be the following (the month list must be adjusted for your specific language):
SET LANGUAGE English
SELECT Datename(m, TheDate)TheMonthName, TheDate, Month(TheDate) TheMonth FROM
(SELECT '19000101' AS TheDate
UNION ALL SELECT '19000201'
UNION ALL SELECT '19000301'
UNION ALL SELECT '19000401'
UNION ALL SELECT '19000501'
UNION ALL SELECT '19000601'
UNION ALL SELECT '19000701'
UNION ALL SELECT '19000801'
UNION ALL SELECT '19000901'
UNION ALL SELECT '19001001'
UNION ALL SELECT '19001101'
UNION ALL SELECT '19001201') TheDates
WHERE 'FebruaryJuneJulyAugustNovemberDecember' LIKE '%' + DATENAME(m, TheDate) + '%'
Results:
Changed language setting to us_english.
TheMonthName TheDate TheMonth
------------------------------ -------- -----------
February 19000201 2
June 19000601 6
July 19000701 7
August 19000801 8
November 19001101 11
December 19001201 12
Best Regards,
Chris Büttner
April 15, 2008 at 3:12 am
kevriley (4/15/2008)
There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right way:DThat's one of the attributes that separate a DBA from a developer!
No, that's one of the attributes that separate a good DBA (or developer) from poor DBA.
A good database developer must know 101 ways to do a trick in sql and which best fits into particular problem regarding performance, side effects, integrity,...
and implementation capabilities of client software. Developer needs not to know about administration tasks.
April 15, 2008 at 3:35 am
You should not use or as per the question but you can use 'and'. So we can build a negative logic like this:
select * from std_tab
where datename(m, dob) <> 1 and datename(m, dob) <> 3 and so on...
Regards,
-Aazad.
April 15, 2008 at 3:44 am
I would never rely on a trick based on a particular feature of the data which wasn't relevant to the selection. In my opinion, this is extremely bad practice.
As written, the selection data does satisfy the requirement that all the months (when displayed in English) have an even number of characters. But this is a mere coincidence. If the requirements change so that the user wants 'March' instead 'February', then someone's got to go and find all those obscure selection clauses and change them to the way they should have been in the first place, since the 'cute trick' doesn't work any more.
I, along with may others, answer YES because of the technical reason that you can always avoid an IN (or OR) by using a JOIN. For example:select *
from stud_mast s
join ( select 2 as mth union all
select 6 union all
select 7 union all
select 8 union all
select 11 union all
select 12) req
on month(s.dob) = req.mth
Of course, an even better way would be to put the required months in a table so that a future update just requires adding, deleting or changing records and you don't have to alter the code at all!
select *
from stud_mast s
join req_month_table r
on month(s.dob) = r.mth
Derek
April 15, 2008 at 5:08 am
Right, but Steve once mentioned that QoD is not about best practices. Some are about bad practice, probably to spark discussions like this, which is OK for me.
If the solution was "correct" in all respects or even mentioned that what was proposed is a bad practice, there would be no discussion.
April 15, 2008 at 5:31 am
Robert (4/15/2008)
Right, but Steve once mentioned that QoD is not about best practices. Some are about bad practice, probably to spark discussions like this, which is OK for me.If the solution was "correct" in all respects or even mentioned that what was proposed is a bad practice, there would be no discussion.
That's true.
The danger is, however, that some people may get the newsletter, read the articles and editorials, answer the QOTD, and never look at the followup discussion as to whether the 'right' answer is actually a good answer.
I think I've mentioned before that I find the most interesting questions are often the ones with wrong or ambiguous answers, because then I often learn something new.
Derek
April 15, 2008 at 5:51 am
Derek Dongray (4/15/2008)
The danger is, however, that some people may get the newsletter, read the articles and editorials, answer the QOTD, and never look at the followup discussion as to whether the 'right' answer is actually a good answer.I think I've mentioned before that I find the most interesting questions are often the ones with wrong or ambiguous answers, because then I often learn something new.
That's life. There are always dangers. If one is so gullible to take it as good practice and not suspect a bit to at least take a look at discussion, he/she should change profession.
Such tricks are good to test trainees or employee candidates.
April 15, 2008 at 6:12 am
kevriley (4/15/2008)
There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right way:DThat's one of the attributes that separate a DBA from a developer!
Correct. The developer will usually get the better way done, while a DBA will usually just say it can't be done.
Mark
April 15, 2008 at 6:38 am
Correct. The developer will usually get the better way done, while a DBA will usually just say it can't be done.
Ouch, although I think that is almost true in a lot of circumstances:
Just the other day a developer's 'solution' entailed enabling xp_cmdshell, switching authentication to mixed mode and creating a sql user in the sysadmin role just to programmatically copy log files from a SQL machine. I said that can't be done! :hehe:
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 15, 2008 at 6:44 am
Based on one case?
It doesn't matter dba or developer. The person matters and there are a lot of different kinds in both baskets. Also, there are many people that are both.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply