Help with Date/Time Arithmetic Problem ...

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)

Help with Date/Time Arithmetic Problem ...

Author

Topic

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-03-23 : 16:14:13

Hi Folks,

Given a start date and a day of the week (e.g Sunday, Monday, Tuesday, etc...) I need to derive the actual date of the "day of the week" looking backwards from the initial start date as far as 1 week.

Examples:
1. Date = Tuesday 3/23/3004; Day = Sunday; Result should be = Sunday 3/21/2004.

2. Date = Tuesday 3/23/2004; Day = Friday; Result should be Friday 3/19/2004.

3. Date = Tuesday 3/23/2004; Day = Tuesday; Result should return Tuesday 3/23/2004 (today's date)

Any ideas? Thanks - wg

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-23 : 16:39:50

Here is a concept you can use:




declare @dayname varchar(25)
declare @daynum int
declare @date datetime

set @dayname = 'Sunday'
set @date = '3/23/2004'

if @dayname = 'Sunday'
 set @daynum = 1
if @dayname = 'Monday'
 set @daynum = 2
if @dayname = 'Tuesday'
 set @daynum = 3
if @dayname = 'Wednesday'
 set @daynum = 4
if @dayname = 'Thursday'
 set @daynum = 5
if @dayname = 'Friday'
 set @daynum = 6
if @dayname = 'Saturday'
 set @daynum = 7

select dateadd(dd, (@daynum - datepart(dw, @date)), @date)

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-03-23 : 16:45:37

This doesn't work ... as any "day of week" greater than the "day of week" of the start date returns a date in the future. For example, if I use today's date and day of week=Friday ... I get 3/26 when I really want to get back 3/19 (the prior Friday).

Any other ideas? Thanks - wg

quote:

Originally posted by drymchaser

Here is a concept you can use:




declare @dayname varchar(25)
declare @daynum int
declare @date datetime

set @dayname = 'Sunday'
set @date = '3/23/2004'

if @dayname = 'Sunday'
 set @daynum = 1
if @dayname = 'Monday'
 set @daynum = 2
if @dayname = 'Tuesday'
 set @daynum = 3
if @dayname = 'Wednesday'
 set @daynum = 4
if @dayname = 'Thursday'
 set @daynum = 5
if @dayname = 'Friday'
 set @daynum = 6
if @dayname = 'Saturday'
 set @daynum = 7

select dateadd(dd, (@daynum - datepart(dw, @date)), @date)

Togaspoon
Starting Member

42 Posts

Posted - 2004-03-23 : 17:01:14

[code]
DECLARE @myDate Datetime,
@myOffset INT,
@myDays INT

/*
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7
*/

SET @myDate = '3/4/04'
SET @myOffset = 4

IF @myOffset - DATEPART(WEEKDAY,@myDate) <= 0
SET @myDays = (@myOffset - DATEPART(WEEKDAY,@myDate))
ELSE
SET @myDays = -7 + (@myOffset - DATEPART(WEEKDAY,@myDate))

SELECT DATEADD(DAY,@myDays,@myDate)
[/code]

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-23 : 17:01:51

Too quick, too quick forgot one little part:

declare @dayname varchar(25)
declare @daynum int
declare @date datetime

set @dayname = 'Wednesday'
set @date = '1/02/2005'

if @dayname = 'Sunday'
 set @daynum = 1
if @dayname = 'Monday'
 set @daynum = 2
if @dayname = 'Tuesday'
 set @daynum = 3
if @dayname = 'Wednesday'
 set @daynum = 4
if @dayname = 'Thursday'
 set @daynum = 5
if @dayname = 'Friday'
 set @daynum = 6
if @dayname = 'Saturday'
 set @daynum = 7

if @daynum > datepart(dw, @date)
	select dateadd(wk, -1, dateadd(dd, (@daynum - datepart(dw, @date)), @date))
else
	select dateadd(dd, (@daynum - datepart(dw, @date)), @date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources