date help - 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)

date help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-15 : 02:02:41

Can anyone help me in either a stored procedure or a sql statement I can select all records between 2 dates --- one always being the friday before it at 12:01
2. the thursday night after it at 11:59

Dates for each record is in a datetime field in sql

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-15 : 06:09:37

Some combination of these should be what you are looking for.

Declare	@CurrentDay	datetime
Select	@CurrentDay	= getdate()

select
	Date
from
	MyTable
where
	Date between
	-- First Friday before a given date at midnight
	dateadd(dd,(datediff(dd,-3,@CurrentDay-1)/7)*7,-3) and
	-- First Thrusday after a given date at midnight
	dateadd(dd,(datediff(dd,-3,@CurrentDay+1)/7)*7,+3)

select
	Date
from
	MyTable
where
	Date between
	-- First Friday on or before a given date at midnight
	dateadd(dd,(datediff(dd,-3,@CurrentDay)/7)*7,-3) and
	-- First Thrusday on or after a given date at midnight
	dateadd(dd,(datediff(dd,-3,@CurrentDay)/7)*7,+3)

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-15 : 15:06:12

thanks for your help

I did:

select
Date
from
MyTable
where
Date between
dateadd(dd,(datediff(dd,-3,getdate()-1)/7)*7,-3) and
dateadd(dd,(datediff(dd,-3,getdate()-1)/7)*7,+3)

How can I change to use a date that I will pass it such as 3/15/2005

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-15 : 15:25:22

It shows that in the code I posted. Just set the value of @CurrentDay to the date you want

quote:
Originally posted by esthera

thanks for your help
...How can I change to use a date that I will pass it such as 3/15/2005...

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-15 : 15:27:47

but I am doing it in an sql statement not a stored procedure and I am running the sql statement from an asp - can I still pass that in?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-15 : 15:47:04

Yes. Example:


dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/15')-1)/7)*7,+3)

Also, why not write a stored procedure? There is no reason why you can't call that from an ASP page.

quote:
Originally posted by esthera

but I am doing it in an sql statement not a stored procedure and I am running the sql statement from an asp - can I still pass that in?

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-16 : 01:22:08

good point! Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources