Create Weeks Number within Specified Date Range

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)

Create Weeks Number within Specified Date Range

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2011-03-01 : 07:12:16

Good day, i need help.

I have a query the returns WeekNumber of year & weekstartdate & weekenddate ranging back 5 years.

But i need to have counter of weeks starting from current back 5 years.

Week is based on Mon to Sun, Now i would like to count weekstartdate + the next weekstart date based on the ResultSet returned from query range.

select

datenumeric,dateweekstart,dateweekend from table

where

dateweekstart between current and back 5 years order by datenumeric desc

datenumeric,dateweek,dateweekstart,dateweekend, NEW Cacl Col Week

20110228 20110228 20110306 1
20110227 20110228 20110306 1
20110226 20110221 20110227 2
20110225 20110221 20110227 2
20110224 20110221 20110227 2
20110223 20110221 20110227 2
20110222 20110221 20110227 2
20110221 20110221 20110227 2
20110220 20110221 20110227 2
20110219 20110214 20110220 3

Please Assist

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-01 : 07:36:07

[code]datediff(wk,dateweekstart,'20110228')+1[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2011-03-01 : 07:49:00

Thank You for helping:

select datediff(wk,dateweekstart,'20110228')+1 from table

Error: Arithmetic overflow error converting expression to data type datetime

Please Assist

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-01 : 08:10:19

Looks like in your table the dateweekstart column is a character datatype such as varchar. It may not be a bad idea to use datetime or smalldatetime data type for those columns which are indeed dates.

Regardless, the error is likely due to one or more entries in the dateweekstart column not being in the YYYYMMDD format. Try this - it can help you identify rows that are in the incorrect format.

select * from YourTable where isdate(dateweekstart) = 0

Or, try

select cast(dateweekstart as datetime) from YourTable

to see if it is failing to cast any record to datetime.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2011-03-01 : 09:03:10

Thank You very much

declare @dte as datetime
set @dte = convert(VARCHAR(8),'20110228',112)

select
datediff(wk,convert(VARCHAR(8),dateweekstart,112),@dte)+1
from table

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-01 : 09:51:48

YVW, glad you were able to get it to work correctly.

I am surprised that you had to explicitly provide a style to do the conversion. YYYYMMDD is supposed to be ISO standard and langauge neutral, and SQL server should have interpreted it correctly regardless of your DATEFORMAT setting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources