User defined function - 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

General SQL Server Forums

New to SQL Server Programming

User defined function

Author

Topic

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 05:34:09

Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 05:43:15

Please define your rules for calculating week #1 for a year.

Peter Larsson
Helsingborg, Sweden

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 05:47:20

Thanks,

my week calculation for example: my week 1 for this current year(2007) is 2007-01-07

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 05:49:52

Because..?

Peter Larsson
Helsingborg, Sweden

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 05:54:34

Hi,

according to the application requirement sunday date shud be the starting day of the week. plz help.

regards,
srikanth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:04:18

Then week #1 for 2007 is 2006-12-31 to 2007-01-06?
Then week #2 for 2007 is 2007-01-07 to 2007-01-13?

What about 2006-01-01? And 2005-01-01?
Which week and year do they belong to?

Peter Larsson
Helsingborg, Sweden

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 06:24:01

Thanks,

Actually my week defines that week #1 for 2007 is 2007-01-01 to 2007-01-07 and i have to get the result as 2007-01-07

week #1 for year 2006 is 2006-01-01
and for week #1 for year 2005-01-02

regards,
Srikanth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:30:43

Are you trying to tell us that WEEK #1 for ANY year is the first seven day period that starts with a sunday and ends with a saturday and the starting sunday is on, or after, Jan 1?

Peter Larsson
Helsingborg, Sweden

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 06:35:58

Thanx,

Absolutely my week #1 is the first sunday date that falls in first seven days of a year. Its little embrassing but the requirement is like that.

Thanks & Regards,
Srikanth.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:36:24

These two statements do not make sense. They are not compatible...

quote:
Originally posted by gsrikanthreddi

according to the application requirement sunday date shud be the starting day of the week.

quote:
Originally posted by gsrikanthreddi

Actually my week defines that week #1 for 2007 is 2007-01-01 to 2007-01-07 and i have to get the result as 2007-01-07

Peter Larsson
Helsingborg, Sweden

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 06:46:11

Assuming the first day is '2007-01-01'
so u can use -- select dateadd(dd,(weekno*6)+1 , '2007-01-01')

You can define varibale to store the datepart and u can use it in the Statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:55:47

It is more complicated than that.

CREATE FUNCTION	dbo.fnGetMyWeek
(
	@WeekNumber TINYINT,
	@Year SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	DECLARE	@EoW DATETIME

	SELECT 	@EoW = DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, @Year - 1900, 0)) / 7 * 7, -1),
		@Eow = DATEADD(DAY, 7 * @WeekNumber - 7, @Eow)

	RETURN	@EoW
END

This will get you the START date of the wanted week. If you want the END date for the wanted week, change

@Eow = DATEADD(DAY, 7 * @WeekNumber - 7, @Eow)

@Eow = DATEADD(DAY, 7 * @WeekNumber - 1, @Eow)

Peter Larsson
Helsingborg, Sweden

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 06:56:18

Hi,
Sorry that i have made u confused on this issue. Actually

quote:
--------------------------------------------------------------------------------
Originally posted by gsrikanthreddi

according to the application requirement sunday date shud be the starting day of the week.
______________________________________________________________________________________

actually iam wrong at my grammer. for week #1,2007 , the result is 2007-01-07.
plz forgive for confusing u.

regards,
srikanth

gsrikanthreddi
Starting Member

14 Posts

Posted - 2007-04-17 : 07:00:41

Thanks a lot for ur help.
It had really solved my problem Peter.

Thanks & regards,
srikanth.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources