Calculate previous week's dates 1

[padinka]

Could someone help out a newbie with a bit of sql?

I need to be able to calculate the previous weeks dates (Sunday through Saturday)no matter what day I run a report using a between statement. (Between 07/28/02 and 08/02/02)

Thank for taking the time to read my post!

Trisha
padinka@yahoo.com

 

[JMCraig]

Hi,

I'm just assuming your dates are stored as Sybase datetime values. If not, this won't help much, but this expression will give you the prior week's Sunday no matter what day of the week you run it on:

dateadd (day
, -7 - (datepart (weekday, getDate()) - 1)
, getdate())

What this does is subtract seven days from the current date and the current prior weekday. In short, on Monday, it'd subtract 8 days; on Friday 12; and so on.

Just by way of explanation, the dateadd function takes a unit day in this example so it's adding days. Since we want to subtract days, we start with a -7 value and go backward beyond that based on what day of the week it is.

datepart returns a part of the date (in this case, the weekday. Since datepart returns 1 for Sunday (such things tend to be 1-based in Sybase), we subtract 1 from that before doing the subtraction from -7 so that we go back the right number of days.

You might find something like this useful:

declare @lastSunday datetime, @lastSaturday datetime
select @lastSunday =
dateadd (day
, -7 - (datepart (weekday, getDate()) - 1)
, getdate()
)
select @lastSaturday = dateadd (day, 6, @lastSunday)

HTH,




J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[padinka]

Thanks! I'll give it a shot
Trisha
padinka@yahoo.com

 

[padinka]

Well, it almost gives me what I need. But it assumes that I'm wanting from last sunday at run time not last Sunday at midnight. Any thoughts on how I can get make it midnight sunday through 11:59:59 saturday?
Trisha
padinka@yahoo.com

 

[padinka]

Yeah!!! Converting the dates to character did the trick. Thanks JM for the code! Here's the final product...

declare @lastSunday datetime, @lastSaturday datetime
select @lastSunday = CONVERT(CHAR(11), dateadd (day
, -7 - (datepart (weekday, getDate()) - 1)
, getdate()
))
select @lastSaturday = CONVERT (CHAR(11), dateadd (day, 1, dateadd (day
, -1 - (datepart (weekday, getDate()) - 1)
, getdate()
)))

Trisha
padinka@yahoo.com

 

[JMCraig]

Well, it seemed to me that you should be able to do this without the conversion to characters, but it appears that without some sort of buildate function, which Sybase doesn't seem to have (a nice stored procedure someone could write maybe), that you can't. In my experimenting, I did come up with this alternate formulation in case you're interested:

declare @now datetime, @lastSunday datetime
select @now = getDate()
select @lastSunday
= dateadd (dd
, ( datepart (dy, @now)
- 7 - datepart(dw, @now) )
, convert ( datetime
, convert ( char(4)
, datepart (yy, @now)
)
)
)
select @now, @lastSunday /* just to show the results */

The approach here is subtly different (if you can follow my unconventional formatting which is a [no doubt misguided] attempt to make the algorithm clear in limited horizontal space!). What this version does is uses the day of the year datepart, applies the adjustment for getting back to the prior Sunday, and then adds that to the datetime created by taking the plain year of the current date (which I can't figure out how to get without a flip into character representation). You'll also notice that this doesn't require the extra - 1 to get back to the prior Sunday; related to how days of the year are 1-based as are days of the week, presumably.

Not as slick as we might like, but hey, presumably you aren't doing this calculation on every line of your script so the performance of it isn't a concern. This itself would make a slick stored procedure....

Glad you're off and running!

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[JMCraig]

Well, of course, after I posted the last message, I figured out that you can avoid the explicit conversion:

/* this expression can be replaced */

convert ( datetime
, convert ( char(4), datepart (yy, @now) )
)
/* by this one */
datename (yy, @now)

Since datename returns the string version, you don't have to return the int version (via datepart) and then convert.

It's shorter ;-)

J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[padinka]

JM,

Thanks for all your help. I wouldn't have been able to do it without you!

Trisha
padinka@yahoo.com