DateTime - 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 2005 Forums

Analysis Server and Reporting Services (2005)

DateTime

Author

Topic

stormcandi
Starting Member

46 Posts

Posted - 2007-04-09 : 18:41:13

Hello all,

I have a parameter I need to pass into a Stored Procedure; however am having some difficulties with the date portion of it. I need for the Calendar to only display Mondays and its dates for the user to select. Or else I need to be able to code it so when a date is selected, the Monday for that week can be used.

The data is available for each week at a time and I need some help to figure out how to ensure only a date for a Monday is passed in the stored procedure. (The table it is being used against has only dates for Mondays in it.)

Thanks in advance for your help!

Candi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-09 : 18:48:11

where datename(weekday, YourDateColHere) = 'monday'

Peter Larsson
Helsingborg, Sweden

stormcandi
Starting Member

46 Posts

Posted - 2007-04-09 : 18:55:04

I am a lot confused since I am new to RS 2005. Where would I put that statement at? What does that statement do?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-09 : 19:02:02

The function on the link below, F_START_OF_WEEK, can be used to convert any date passed to it to the Monday on or before the date passed.

Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


select 
	a.Date,
	Monday = dbo.F_START_OF_WEEK(a.Date,2)
from
	(
	-- Create test date
	Select Date = getdate()			union all
	Select Date = getdate()+1		union all
	Select Date = getdate()+2		union all
	Select Date = getdate()+3		union all
	Select Date = getdate()+4		union all
	Select Date = getdate()+5		union all
	Select Date = getdate()+6		union all
	Select Date = getdate()+7		union all
	Select Date = getdate()+8
	) a


Results:

Date                     Monday
-----------------------  -----------------------
2007-04-09 18:54:44.830  2007-04-09 00:00:00.000
2007-04-10 18:54:44.830  2007-04-09 00:00:00.000
2007-04-11 18:54:44.830  2007-04-09 00:00:00.000
2007-04-12 18:54:44.830  2007-04-09 00:00:00.000
2007-04-13 18:54:44.830  2007-04-09 00:00:00.000
2007-04-14 18:54:44.830  2007-04-09 00:00:00.000
2007-04-15 18:54:44.830  2007-04-09 00:00:00.000
2007-04-16 18:54:44.830  2007-04-16 00:00:00.000
2007-04-17 18:54:44.830  2007-04-16 00:00:00.000

(9 row(s) affected)

CODO ERGO SUM

stormcandi
Starting Member

46 Posts

Posted - 2007-04-09 : 19:17:45

Gotcha! Thanks MVJ!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources