• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

SQL Stored procedure converting date to week number as variable

Hi,
I need help writing a stored procedure that takes the date entered and converts it to a week number stored as a variable which is then passed into a sql statement. Example: user enters 4/6/2009 as a date input it returns week 15 as @week, which is then used in a select statement:
select * from dbtest where createddate = @week. Any assistance is greatly appreciated. Thanks.

Claude
0
cbads
Asked:
cbads
  • 5
  • 3
1 Solution
 
udaya kumar laligondlaTechnical LeadCommented:
select datediff(dd,cast(datePart(yyyy,FieldName) as varchar) + '-'+'01'+'-'+'01',FieldName ) / 7 from tablename will give you the no of weeks
0
 
BrandonGalderisiCommented:
Do you want your week to represent 7 days from Sunday to Saturday of any given entered date?
0
 
BrandonGalderisiCommented:
--Sample input date:
declare @InputDate datetime
set @InputDate = '2009-04-06'

--This removes the time component.
set @inputdate = dateadd(d, datediff(d, 0, @inputdate),0)
--This translates @Inputdate into Sunday of that week.
select @inputdate = @InputDate-(((@@datefirst+datepart(dw, @inputdate))%7)-1)

-- Now you say >= Sunday at midnight and < Next Sunday.
select * from dbtest
where createddate >= @week
and createddate  < @week-7
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
BrandonGalderisiCommented:
FYI:  I've wrapped the logic inside of a UDF and posted it over on our site at SQL Server Nation.

http://sqlservernation.com/blogs/howtos/archive/2009/04/06/how-to-determine-the-sunday-of-a-given-week.aspx
0
 
cbadsAuthor Commented:
Yes, I would like the weeks represented from Sunday to Saturday using a date entered by the user to be inputed as a variable used in a stored procedure. Thanks
0
 
BrandonGalderisiCommented:
in post http:#a24078412

The last line:
and createddate  < @week-7

should be:
and createddate  < @week+7

Other than that, try what I've already posted.
0
 
cbadsAuthor Commented:
Thanks, it worked perfectly but I was hoping to use the @week variable against a field that has the created date already calculated with the week number in a table...so when the user enters the start date of the week(2009-04-05)....it will convert it to a week number(15) which is then used in the select query : Select * from dbtable where columna = @week. Sorry if I didn't explain it better in the initial post.
Claude
0
 
BrandonGalderisiCommented:
So what happens if they pass January 1, 2009?  That's a Thursday.  And only January 1-3 will have Week Number 1, December 28-31 will have week 53 from the previous year.
0
 
cbadsAuthor Commented:
I see your point, I guess using the week number would not work in that example. Thanks again for your help. I'll use the previous example where createddate >= @week
and createddate  < @week+7.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now