Temporal question - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Temporal question

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2008-07-11 : 05:31:38

Hi Folks

Table has field MyDateTime datetime

Query has input of @InDateTime datetime

Need to select * where MyDateTime's (3rd Friday in month) = @InDateTime(which equates to 3rd Friday in month)

The InDateTime could of course be Nth occurrence in it's month

I'm sure the answer must be here somewhere but being new to this forum I haven't been able to find it after reading for several hours! Fascinating reading by the way

Many thanks
Andy

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-11 : 05:34:00

sorry.... what's the question?

Em

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-11 : 05:39:49

take a look at this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515

also look at anything in Script Library forum by Michael Valentine Jones.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

ultradiv
Starting Member

41 Posts

Posted - 2008-07-11 : 05:40:10

quote:
Originally posted by elancaster

sorry.... what's the question?

Em

The parameter contains a datetime value and I want to select from the table the datetime values that eqaute to the same occurrence of that parameters day e.g. the second monday in the month

I am finding it difficult to explain - sorry

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-11 : 05:46:20

so you want to match not on date but on 'position in the month' ? i.e. if the parameter date equates to the '1st monday in a month' you want to return all the '1st monday in a month' ? any month? any year?

can you provide some sample data to help with an example of you would like to return / not return?

Em

ultradiv
Starting Member

41 Posts

Posted - 2008-07-11 : 05:52:54

quote:
Originally posted by elancaster

so you want to match not on date but on 'position in the month' ? i.e. if the parameter date equates to the '1st monday in a month' you want to return all the '1st monday in a month' ? any month? any year?

can you provide some sample data to help with an example of you would like to return / not return?

Em

Yes Exactly

The data is very simple so I dont really have a lot to offer you but:

select CleanID
from dbo.WR_schedule
where @dateIN = CleanStart

the where clause is what needs attention

thanks elancaster

ultradiv
Starting Member

41 Posts

Posted - 2008-07-11 : 06:09:57

One other thing, I am using set @@DATEFIRST = 1
in SQL Server 2000

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-11 : 06:17:23

struggling for time today so i've not had chance played with it much yet, but as per 'spirit1's suggestion this is a link to a date table function in the script library...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1

something like that would let you quickly determine 'where in the month' you are. It's a start anyway

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 01:23:51

try this too (not tested)


select * where DATEPART(dw,MyDateTime)  = DATEPART(dw,@InDateTime)
AND DATEPART(wk,MyDateTime)=DATEPART(wk,@InDateTime)

ultradiv
Starting Member

41 Posts

Posted - 2008-07-12 : 18:31:13

quote:
Originally posted by visakh16

try this too (not tested)

select * where DATEPART(dw,MyDateTime) = DATEPART(dw,@InDateTime)
AND DATEPART(wk,MyDateTime)=DATEPART(wk,@InDateTime)

No, the wk is 'of year' and I need to select from any month any year

ultradiv
Starting Member

41 Posts

Posted - 2008-07-12 : 19:25:26

I think the solution may be very simple.

WHERE ((DATEPART(d,DateColumn)-1)/7)+1 = ((DATEPART(d,@dateIN)-1)/7)+1
and DATEPART(dw,DateColumn) = DATEPART(dw,@dateIN)

It seems to work, ((number of day in month)-1)/7)+1

or am I missing something here?

Q. Does using the DATEPART function on an indexed field nullify the index when selecting?
If so
Q. Is there a way around this to maintain the fastest access when selecting from this data in this manner?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources