Same Date Last Year 2

DennisTheMenace · Nov 28, 2007

There is a GREAT FAQ on dates in SQL found here by Robyn Page

According to the FAQ, DATEPART allows you to figure out what week and day it is:

FAQ said:
SELECT DATEPART(YEAR,GETDATE()) --2006
SELECT DATEPART(quarter,GETDATE()) --4
SELECT DATEPART(MONTH,GETDATE()) --10
SELECT DATEPART(dayofyear,GETDATE()) --285
SELECT DATEPART(DAY,GETDATE()) --12
SELECT DATEPART(week,GETDATE()) --42
SELECT DATEPART(weekday,GETDATE()) --4
SELECT DATEPART(hour,GETDATE()) --9
SELECT DATEPART(minute,GETDATE()) --32
SELECT DATEPART(second ,GETDATE()) --8
SELECT DATEPART(millisecond,GETDATE()) --875

Does anyone know how to figure out the same date last year based on that? For example the above date used is: October 12th, 2006 which DATEPART breaks down as Thursday (weekday 4) of the 42nd Week of the year 2006.

With that information, I need to go the OTHERWAY. I am trying to get the date that would match in the previous year: The Thursday of the 42nd Week in the year 2005.

Obviously we don't just subtract 365...

Thanks in advance for any help!

-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

AlexCuse · Nov 28, 2007

use DateAdd.

Are you any relation to Denis the SQL Menace?

[small]----signature below----[/small]
You can't fit a square data in a round table

DennisTheMenace · Nov 28, 2007

sorry... no relation! ;o)

AlexCuse said:
use DateAdd

And DateAdd -52 weeks? Will it land on a Thursday such as the example?

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

kaht · Nov 28, 2007

There's probably a better way to do this, but here's what I got:

Code:

[COLOR=green]--in which year?
[/color][COLOR=blue]declare[/color] @yr [COLOR=blue]smallint[/color]
[COLOR=blue]set[/color] @yr = 2006
[COLOR=green]--which week do you want to find?
[/color][COLOR=blue]declare[/color] @wk [COLOR=blue]tinyint[/color]
[COLOR=blue]set[/color] @wk = 42
[COLOR=green]--which day do you want to find 1 - 7 (sun - sat)
[/color][COLOR=blue]declare[/color] @dy [COLOR=blue]tinyint[/color]
[COLOR=blue]set[/color] @dy = 5 [COLOR=green]--thurs
[/color]
[COLOR=blue]declare[/color] @a [COLOR=blue]smalldatetime[/color]
[COLOR=blue]set[/color] @a = [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](4), @yr) + [COLOR=red]'0101'[/color]

[COLOR=blue]declare[/color] @b [COLOR=blue]smalldatetime[/color]
[COLOR=blue]set[/color] @b = [COLOR=#FF00FF]dateadd[/color](dd, (@wk - 2) * 7 + (@dy - 1), [COLOR=#FF00FF]dateadd[/color](dd, 8 - [COLOR=#FF00FF]datepart[/color](dw, @a), @a))

[COLOR=blue]select[/color] @b, [COLOR=#FF00FF]datepart[/color](dw, @b), [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]week[/color], @b)

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

kaht · Nov 28, 2007

do take note that you're able to come up with dates outside of the year you're expecting

for instance, if you try to find the sunday of the first week in 2007, it returns 2006-12-31 since there was no sunday in the first week of 2007

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

AlexCuse · Nov 28, 2007

here's a more specific example:

Code:

[COLOR=blue]declare[/color] @d [COLOR=#FF00FF]datetime[/color], @newday [COLOR=#FF00FF]datetime[/color], @startyear [COLOR=#FF00FF]datetime[/color],@wk [COLOR=blue]int[/color], @day [COLOR=blue]int[/color]

[COLOR=blue]set[/color] @d = [COLOR=red]'20061012'[/color]


[COLOR=green]--capture week/year
[/color][COLOR=blue]select[/color] @wk = [COLOR=#FF00FF]datepart[/color](wk, @d)
	, @day = [COLOR=#FF00FF]datepart[/color](dw, @d)

[COLOR=green]--determine baseline to calculate new date from (based on year-1)
[/color][COLOR=blue]set[/color] @startyear = [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]year[/color](@d) - 1 [COLOR=blue]as[/color] [COLOR=blue]char[/color](4)) + [COLOR=red]'0101'[/color]

[COLOR=green]--display initial date
[/color][COLOR=blue]select[/color] [COLOR=red]'initial date'[/color], @day, @wk, @d 

[COLOR=green]--get final date
[/color][COLOR=blue]select[/color] @newday = [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], @day, 
	[COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]week[/color], @wk -1, @startyear 
	- [COLOR=#FF00FF]datepart[/color](dw, @startyear)))

[COLOR=green]--display final date
[/color][COLOR=blue]select[/color] [COLOR=red]'result date'[/color], [COLOR=#FF00FF]datepart[/color](dw, @newday), [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]week[/color], @newday), @newday

Let me know if you have any questions.

Alex

[small]----signature below----[/small]
You can't fit a square data in a round table

AlexCuse · Nov 28, 2007

whoa, slow trigger finger on that one

Good to see there's another cowboy in this ghost town...

[small]----signature below----[/small]
You can't fit a square data in a round table

kaht · Nov 28, 2007

Yeah, but your version is much simpler, you didn't go to the trouble of finding the sunday of the first full week and all that other garbage.....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

AlexCuse · Nov 28, 2007

Eh, have a star anyway, pardner!

Dennis - I am not sure I would trust -52 weeks. If you decide to go that route, be sure to test it well. And try adding this to the script I posted.

Code:

[COLOR=blue]select[/color] [COLOR=red]'minus 52'[/color], [COLOR=#FF00FF]datepart[/color](dw, [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]week[/color], -52, @d)), [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]week[/color], [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]week[/color], -52, @d)), [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]week[/color], -52, @d)

[small]----signature below----[/small]
You can't fit a square data in a round table

DennisTheMenace · Nov 28, 2007

Excellant info!! Thank you both so much!! I needed to compare sales data (count and $$) from today vs. last year. My stored procedure ended up looking like this (note that I had to end up CAST the final date due to the format the TABLE had that field stored in:

Code:

CREATE PROCEDURE sp_SOrdersLastYear AS

declare @d datetime, @newday datetime, @startyear datetime,@wk int, @day int

set @d = getdate()

--capture week/year
select @wk = datepart(wk, @d), @day = datepart(dw, @d)

--determine baseline to calculate new date from (based on year-1)
set @startyear = cast(year(@d) - 1 as char(4)) + '0101'

--display initial date
--select 'initial date', @day, @wk, @d 

--get final date
select @newday = dateadd(day, @day, 
    dateadd(week, @wk -1, @startyear 
    - datepart(dw, @startyear)))

--display final date

select 
	count(*) as myCount, sum(ORDER_TOTAL) as myDollar, @newday as myResult
from 
	ORDERTABLE 
where 
	ORDERTABLE.the_status = 'S'
	AND
	ORDERTABLE.the_date = 
(
CAST(
	(
		STR( YEAR( @newday ) ) + '/' +
		STR( MONTH( @newday ) ) + '/' +
		STR( DAY( @newday ) )
	)
	AS DATETIME 
	)
)
GO

Thanks again for your very informative (and extremely speedy) assistance! Too bad there isn't a way to add this info into that FAQ?!

-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

ESquared · Nov 28, 2007

What if it's the 53rd week of the year and last year doesn't have a week 53? What do you do then?

DennisTheMenace · Nov 28, 2007

The compare would be 0 count and $0.00 as there was NO 53rd week last year. Not sure what SQL would return for myResult (the calculated day) though?

Anyone know a date in the 53rd week of the year that I can run the script against?

-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

kaht · Nov 28, 2007

Anyone know a date in the 53rd week of the year that I can run the script against?

Should be pretty easy to find considering it would have to be near the very end of the year.....

Code:

select datepart(week, '2006-12-31')

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

kaht · Nov 28, 2007

Eh, have a star anyway, pardner!

How sweet of you Alex..... stars for everybody, let's make a party out of it!

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

DennisTheMenace · Nov 28, 2007

December 31, 2005 is the 7th day of week 53.

The script returns @newDay as 01/01/2005.

So... my guess is that we should add a check for @newday's year??

If it is the same year as DATEPART(YEAR,GETDATE()), then we need to disregard it??? Or perhaps SQL always returns 01/01 of the current year when there is an issue??

-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

RiverGuy · Nov 28, 2007

Joining this thread late, but, having created dozens and dozens of reports with Same Day Last Year logic, in my opinion subtracting 52 weeks is the most relevant comparison if you are going for the same day of the week--even if both dates are in the same calendar year. However, users can be confused as to what date you actually used, so it's best to put the date in the report header regardless of which method is used.

DennisTheMenace · Nov 28, 2007

Thanks for the heads up RiverGuy! I actually do that already. My ASP Page shows BOTH DATES.

Here is the header on today's ASP page:

ORDERS SO FAR TODAY
Wednesday, November 28, 2007
--------------------------------------
COUNT VALUE
--------------------------------------

SHIPPED ORDERS LAST YEAR
Wednesday, November 29, 2006
--------------------------------------
COUNT VALUE
--------------------------------------

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

gmmastros · Nov 28, 2007

This is getting a bit complicated, don't you think. It may be simpler to 'hard code' last years date. More specifically...

I recommend you add another table to your database. This table should have 2 DateTime columns. One for CurrentDate and another for LastYearsDate. Then, add records to this table for an exhaustive time range (1 Jan 1990 to 1 Jan 2200). Then, you can simply join to this new table to get last years date. The best part is... you can show your table to your boss and let him decide what to do about the goofy week/day problems.

Now, consider that you may want to have a couple hundred years worth of data in this table. That's no problem. 2 hundred years worth of data will be less than 80000 rows, which is ridiculously small, and properly indexed will perform extremely well (maybe even better than all thos functions).

Just something to think about. [smile]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

ESquared · Jan 8, 2008

By the way, if you're just subtracting 52 weeks, it's more efficient to simply do

Code:

SELECT TheDate - 364 -- 52 weeks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Same Date Last Year 2

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor