Fixing query that uses built in date functions

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)

Fixing query that uses built in date functions

Author

Topic

bi89405
Starting Member

35 Posts

Posted - 2009-12-15 : 11:11:19

Hi Guys,

I need some help.

I created the following query below:
The #weektable acts as the LEFT most table in a big query. The graph shows 4 weeks, last week, this week, next week, and two weeks from now.

However, if you run the query below, you'll notice that two weeks from now returns Week # 53 when it should be Week # 1 for the beginning of the new year.

Can anyone please please help me figure this out? Thank you in advance.

SELECT DATEPART(YEAR,getdate()) AS 'Year',
DATEPART(wk,getdate())-1 AS 'Week #' into #weektable
insert into #weektable
SELECT DATEPART(YEAR,getdate()) AS 'Year',
DATEPART(wk,getdate()) AS 'Week #'
insert into #weektable
SELECT DATEPART(YEAR,getdate()) AS 'Year',
DATEPART(wk,getdate())+1 AS 'Week #'
insert into #weektable
SELECT DATEPART(YEAR,getdate()) AS 'Year',
DATEPART(wk,getdate())+2 AS 'Week #'

Regards,
Zack H.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 12:08:37

This year has week 53!!

But anyways..your logic seems to be incorrect as you are just adding to the week number returned..

Try this..

SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,dateadd(wk,-1,getdate())) AS 'Week #' into #weektable 
insert into #weektable 
SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,getdate()) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,dateadd(wk,1,getdate())) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,dateadd(wk,2,getdate())) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,dateadd(wk,3,getdate())) AS 'Week #'

bi89405
Starting Member

35 Posts

Posted - 2009-12-15 : 12:19:39

Thanks for your reply.
However, your Year value did not change to 2010 for Week # 2.

Any ideas?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 13:54:15

Yeah..sure..

i forgot to add that piece for the year..just did it for the week..
Try this..

SELECT DATEPART(YEAR,dateadd(wk,-1,getdate())) AS 'Year', 
DATEPART(wk,dateadd(wk,-1,getdate())) AS 'Week #' into #weektable 
insert into #weektable 
SELECT DATEPART(YEAR,getdate()) AS 'Year', 
DATEPART(wk,getdate()) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,dateadd(wk,1,getdate())) AS 'Year', 
DATEPART(wk,dateadd(wk,1,getdate())) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,dateadd(wk,2,getdate())) AS 'Year', 
DATEPART(wk,dateadd(wk,2,getdate())) AS 'Week #' 
insert into #weektable 
SELECT DATEPART(YEAR,dateadd(wk,3,getdate())) AS 'Year', 
DATEPART(wk,dateadd(wk,3,getdate())) AS 'Week #'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources