DATING ISSUES... - 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

SQL Server Development (2000)

DATING ISSUES...

Author

Topic

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-10-01 : 18:30:29

What I need to do is to compare days of the month by year..not dates of the month, but days..so July 3 of 2003 will match with Jul 1 of 2004 because they are both on Thursday... Ok now it gets difficult, if Jul 1, 2003 was on a Tuesday then I need to match Jun 29, 2004 with it because they were both on Tuesday and the next corresponding Tuesday in July isn't until Jul 6, 2004. Ok, so here's my trouble..Generally if the day falls on a Saturday, my code adds 7 days to it also if the day falls on a Friday it is the same thing, but it's not all Sat. or Fri. it's some.

Any improvements on the code or my situation? There are three badly named variables that I am using @WEEK_DAY, @PREV_WEEK_DAY, @NEW_WEEK... @WEEK_DAY will hold days for the prior year, @PREV_WEEK_DAY holds values for the current year and @NEW_WEEK holds the new values that I stuffing in it based on the prev. year.

DECLARE @DAY INT,
@MO TINYINT,
@YR INT,
@WEEK int,
@PREV_WEEK INT,
@COUNT INT,
@WEEK_DAY VARCHAR(35),
@PREV_WEEK_DAY VARCHAR(35),
@DATE datetime,
@MONTH INT,
@MONTH_COUNTER INT,
@DAY_COUNTER INT,
@CHAR_MONTH VARCHAR(35),
@MSG VARCHAR(1000),
@NEW_WEEK VARCHAR(35)

SET @DAY = 0
SET @COUNT = 0
SET @PREV_WEEK = 0
SET @MONTH = 0
SET @MONTH_COUNTER = 1
SET @DAY_COUNTER = 1

WHILE @MONTH < 12
BEGIN

SET @MONTH = @MONTH + 1

SET @DATE = '2003' + '-' + CONVERT(VARCHAR(20), @MONTH) + '-' + '01'
PRINT CONVERT(VARCHAR(35), @DATE)

SET @MO = MONTH(@DATE)
SET @YR = YEAR(@DATE)

WHILE @DAY < DAY(DATEADD(d,-1,CONVERT(char(2),MONTH(DATEADD(mm,1,@DATE)))+'/01/'+CONVERT(char(4),YEAR(@DATE))))
BEGIN
SET @DAY = @DAY + 1

SET @WEEK_DAY = CONVERT(VARCHAR(2), @MO) + '/' + CONVERT(VARCHAR(2), @DAY) + '/' + CONVERT(VARCHAR(4), @YR)
SET @PREV_WEEK_DAY = CONVERT(VARCHAR(2), @MO) + '/' + CONVERT(VARCHAR(2), @DAY) + '/' + CONVERT(VARCHAR(4), @YR + 1)

IF @WEEK_DAY < @PREV_WEEK_DAY
BEGIN
SET @DAY_COUNTER = DATEPART(dw, @WEEK_DAY) - DATEPART(dw, @PREV_WEEK_DAY)
SET @NEW_WEEK = DATEADD(dw, @DAY_COUNTER, @PREV_WEEK_DAY)
END
ELSE
IF @WEEK_DAY > @PREV_WEEK_DAY
BEGIN
SET @DAY_COUNTER = DATEPART(dw, @WEEK_DAY) - DATEPART(dw, @PREV_WEEK_DAY)
SET @NEW_WEEK = DATEADD(dw, @DAY_COUNTER, @PREV_WEEK_DAY)
END

SET @MSG = CONVERT(CHAR(20), @WEEK_DAY + ' ' + DATENAME(dw, @WEEK_DAY)) + SPACE(10) + CONVERT(CHAR(20), @PREV_WEEK_DAY + ' ' + DATENAME(dw, @PREV_WEEK_DAY)) + SPACE(10) + @NEW_WEEK + ' ' + DATENAME(dw, @NEW_WEEK)

SET @WEEK = DATEPART(wk, @WEEK_DAY)-DATEPART(wk, DATEADD(d, 1-DATEPART(d, @WEEK_DAY), @WEEK_DAY))+1

IF @WEEK <> @PREV_WEEK
BEGIN
PRINT CONVERT(CHAR(1), @WEEK)
END

PRINT @MSG

SET @PREV_WEEK = @WEEK
END

SET @DAY = 0

END

Derrick

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-10-01 : 18:32:32

BTW, the @PREV_WEEK variable can be used as a calendar for checking the NEW_WEEK variable...

Derrick

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-02 : 08:40:10

you really overcomplicated this.
search the google for DoomsDay algorithm. this will solve your problems in seconds.
Basically:
the last day of february (28th or 29th) is the same day as:
d.m.y format
2.1.
1.3. = last day of feb + 1
4.4.
6.6.
8.8.
10.10.
12.12.
9.5.
5.9.
7.11.
11.7.

which day of the week is the dooms day can also be calcualted.
check this out:
http://rudy.ca/doomsday.html

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

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-02 : 09:43:53

[code]select dateadd(wk,case when datename(dw,@myCurrentDate)='saturday' or datename(dw,@myCurrentDate)='friday' then 53 else 52 end,@myCurrentDate)[/code]
If I understand correctly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources