Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Funny Test III

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 21:34:29

Find the 3rd monday of the month when given any date in that month.

i.e. any date in september 04 should resolve to 9/20/04

Use only:

Using 1 select

no subqueries

only 3 function calls [or less] (left(),Left(),right() counts as three)


Declare @myDate datetime

Set @myDate = '9/3/2004'

Set @myDate = '5/28/2005'

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-06 : 00:23:36

3 Function calls here (Remember that the GETDATE() that I am using is populating the variable to test it could obviously be replaced with a hard-coded date).

[CODE]
SET DATEFIRST 1
DECLARE @MYDate DATETIME
DECLARE @MAX DATETIME

SET @MYDate = GETDATE()
SET @MAX = CAST(CONVERT(VARCHAR(7), @MYDate, 121) + '-21' as DATETIME)

SELECT @MAX - (DATEPART(DW, @Max) - 1)

GO
[/CODE]

Duane.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-06 : 04:39:27

Clever Duane!

My answer is lame in comparison but it only has 2 functions and I wanted to see if I could do it this way.

Declare @myDate datetime
Set @myDate = '9-25-2004'

Declare @count int, @mondays int, @aday datetime, @thirdmonday datetime

select top 21
@count = Case when @count Is Null then 1 else @count + 1 end,
@mondays = case when @mondays is null then 0 else @mondays end,
@aday = @mydate - datepart(day,@mydate) + @count,
@mondays = Case When datename(weekday,@aday) = 'Monday' THEN @mondays + 1 else @mondays end,
@thirdmonday = case when @mondays = 3 and @thirdmonday is null then @aday else @thirdmonday end
from sysobjects

select @thirdmonday

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 07:45:07

Ok, Didn't manage to do it in less than three...


SET DATEFIRST 1
DECLARE @myDate DATETIME

SET @myDate = '9/3/2004'

SELECT @myDate+1-DATEPART(dw,@myDate)+7*(3-(7+DAY(@myDate)-DATEPART(dw,@myDate))/7) AS ThirdMonday

SET @myDate = '5/28/2005'

SELECT @myDate+1-DATEPART(dw,@myDate)+7*(3-(7+DAY(@myDate)-DATEPART(dw,@myDate))/7) AS ThirdMonday

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 07:51:06

Ditch,

Aren't You cheating

!?
I don't think a SET operation is allowed, The @MAX variable would have to expand in a derived table or something to avoid the SET!

Kselvia, that was wicked

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-06 : 07:56:59

OK!
Then you are cheating too RockMoose

quote:
SET DATEFIRST 1

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 08:01:23

quote:
Originally posted by ditch

OK!
Then you are cheating too RockMoose
quote:
SET DATEFIRST 1

Duane.

But you are cheating twice!
and once more with the declare

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-06 : 08:04:03

Alright then where in the rules does it say that the set operation is not allowed?

quote:

Use only:
Using 1 select
no subqueries
only 3 function calls [or less] (left(),Left(),right() counts as three)

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 08:09:16

quote:
Originally posted by ditch

Alright then where in the rules does it say that the set operation is not allowed?
quote:

Use only:
Using 1 select
no subqueries
only 3 function calls [or less] (left(),Left(),right() counts as three)

Use only: <- Here it says

...

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-06 : 08:14:31

Ha! Ha!

Your's is quite a good solution though.

I tried to find fault with it (You know for our personal war), but I couldn't.
Nice One!

Duane.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 08:23:11

quote:
Originally posted by ditch

Your's is quite a good solution though.

I tried to find fault with it (You know for our personal war), but I couldn't.

I would have found that terribly embarassing, of course

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-06 : 09:37:39

The answer I was looking for was:


Declare @myDate datetime

Set @myDate = '9/3/2004'

Select thirdMonday = (@myDate - day(@myDate)) + ((9-datepart(dw,@myDate-day(@myDate)))%7+14)

Set @myDate = '5/28/2005'

Select thirdMonday = (@myDate - day(@myDate)) + ((9-datepart(dw,@myDate-day(@myDate)))%7+14)

Rockmoose managed it inside the params.... so he wins!!! His turn to post one

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-06 : 10:30:29

don't mean to be rude

corey but i don't get the 3rd monday wih your code.
i get it with this:

Select thirdMonday = (@myDate - day(@myDate)) + ((8-datepart(dw,@myDate-day(@myDate)))%7+14)

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-06 : 10:40:39

oh... I would guess its becuase monday gives a datepart(dw,@aMonday)=1 on your server. I left mine on the default which is monday is the 2nd day of the week... thus the 9.

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-06 : 10:44:32

yeah. it dawned on me 10 secs after i posted this... go figure

BTW nice thing with %.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-06 : 10:45:52

spirit- you've been active lately... got a puzzle for us?

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 16:44:15

Ok,

I have posted one for You guys.

[url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=39490[/url]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 16:49:20

By the Way...

I WIN !!!

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-06 : 19:29:21

Surely this should be written to work with any datefirst value?
I'm sure I've posted somewhere how to get the first monday with any datefirst setting - the third follows.

Maybe that should be another quiz.
I always find it a pain to work out - should kkep it somewhere.

Will take more than 3 functions though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-06 : 20:12:36

Hmm don't remember using a hard coded date last time but this is easier to remember (or at least work out)
something like

select (@d - datepart(dd,@d) + 1) - datediff(dd, '17530101',@d - datepart(dd,@d) + 1) % 7 + 21

It's worth remembering that both
select datename(dw,'17530101')
select datename(dw,'19000101')
= 'Monday'

so
select @d1 = (@d - datepart(dd,@d) + 1) - convert(int,@d - datepart(dd,@d) + 1) % 7 + 21

will do the same thing
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-06 : 21:01:56

Something like this???


Declare @d datetime,
	@dw int,
	@dayCnt int

Set @d = '9/4/2004'
Set @dw = 2 --Monday==2
Set @dayCnt = 3 -- third Monday

select thirdMonday = (@d - day(@d) + 1) + (7 + @dw - datepart(dw,@d - day(@d) + 1)) % 7 + (7*(@dayCnt-1))

Set @dw = 5 --Thursday
Set @dayCnt = 2 -- Second Thursday

select secondThursday = (@d - day(@d) + 1) + (7 + @dw - datepart(dw,@d - day(@d) + 1)) % 7 + (7*(@dayCnt-1))

Corey

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources