Datepart(Wk) problem - 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 2005 Forums

Transact-SQL (2005)

Datepart(Wk) problem

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-12-29 : 09:10:55

hi

I have few questions here and these are my 2 sample code:

Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate) = datepart(wk, getDate())

Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate) = datepart(wk, getDate()) + 1

1) This week have 53 and 1. How do i combine this cause it only shows 29 to 31 but not Jan 2 2009

2) How do I do the next week to see next week trade when WK is aleady 53?

The second code works fine until i am hit with week 53.

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 09:24:39

Why are you using getdate() ? I dont quite understand what you are trying to do.
Neverthless, Look at the code below. Hopefully it helps.

--to see trade for week 52,53 for any year
Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate)  in (52,53)
--to see trade for week 1 for any year
Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate)  =1
--to see trade for week 52,53 for  year=2008
Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate)  in (52,53) and datePart(wk,dbo.tblTrade.StartDate)=2008
--to see trade for week 52,53 for  year=2008
Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate)  in (52,53) and datePart(wk,dbo.tblTrade.StartDate)=2008
--to see trade for week 1 for  year=2009
Select StartDate from tblTrade
where datePart(wk,dbo.tblTrade.StartDate)  =1 and datePart(wk,dbo.tblTrade.StartDate)=2009

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-12-29 : 09:42:01

hi

firstly, thanks for the reply

The reason i am using getdate is because in the ASPNET page it suppose to list this week trades and there is a button to show next week trades.

Anyway, how do i combine week 53 and 1 since it happen to have week 53 and 1 in the last week of 2008?

Secondly, how do i add 1 week to see the next week trades when it is week 53 of 2008 and it is already week 1 this thurday Jan 1 2009?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:03:51

for this weeks trade use


Select StartDate from tblTrade
where dbo.tblTrade.StartDate>= dateadd(wk,datediff(wk,0,getDate()),0)
and dbo.tblTrade.StartDate<dateadd(wk,datediff(wk,0,getDate())+1,0)

for next weeks use


Select StartDate from tblTrade
where dbo.tblTrade.StartDate>= dateadd(wk,datediff(wk,0,getDate())+1,0)
and dbo.tblTrade.StartDate<dateadd(wk,datediff(wk,0,getDate())+2,0)

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-12-29 : 12:57:35

Thanks a million

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:58:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources