SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Query


T-SQL Query

Author
Message
Raghav-673586
Raghav-673586
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 8
Comments posted to this topic are about the item T-SQL Query
VALEK
VALEK
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 462
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
Ian Lacey
Ian Lacey
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 20
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.
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3110 Visits: 2618
There is always 101 ways to do anything in t-sql - the trick (or more specifically, the skill) is knowing which is the right wayBigGrin

That's one of the attributes that separate a DBA from a developer!
AC Lupu
AC Lupu
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 106
select * where month = 2
union all
select * where ...
...
???
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3555 Visits: 3889
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
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1764 Visits: 1010
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 wayBigGrin

That'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.
chandrasekhar.ms
chandrasekhar.ms
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 53
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.
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 1992
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
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1764 Visits: 1010
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.
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