How to get month number in sql by passing the week number

Question

This is my function as below

CREATE FUNCTION  getmonth
(        

 @startdate  datetime,@Week int
)        
RETURNS  int        
AS        
BEGIN                      
 declare @year int,@Month int;              
 declare @sdate  datetime=@startdate;       
 set @year= year(CAST(@startdate as date));  
 set @sdate = DATEADD(wk, DATEDIFF(wk, 6,  '1/1/' + CAST( @year as varchar(4)) ) + (@Week-1) ,1);           
  SET @Month = month( @sdate);              
 return @month        

END

I Just want this function to return the month number when we pass a week number of the year and week start date of year.

Week should be start on Sunday and ends at Saturday.

Example

For Year 2015,Week start date is 12/28/2014.When we pass week number it should return the month number of year 2015.

So, for a given week number, you want to know in which month falls the Thursday of that week? — Strawberry, Jul 01 '15 at 05:59

score 1 · Answer 1 · edited May 23 '17 at 12:33

1

Assuming that 1st week starts at 2015-01-01 (Or 1st of Jan each year) and you are using MySQL.

This query will return the Month number using the week.

select DATE_FORMAT( DATE_ADD('2015-01-01', INTERVAL 5 WEEK) ,'%m')

Here, in 2015-01-01 is starting date. In INTERVAL 5 WEEK the number 5 is the week number that you will provide

Result: 02 i.e. the second month of the year!

Using:

https://stackoverflow.com/a/11423781/3578289

http://www.w3schools.com/sql/func_date_format.asp

edited May 23 '17 at 12:33

Community

1
1

answered Jul 01 '15 at 05:54

eMad

998
3
13
31

1

you should mention that you are assuming that OP wants this in mysql as the code above only works in mysql and not on sql server – ughai Jul 01 '15 at 06:13

Vinay Guru · Answer 2 · 2015-07-01T07:18:41.163

0

select now(),curdate(),curtime()

There is no current month or current week,but there is a query to know current date and time.. Use these example query shown below.

Example:- CREATE TABLE getmonth ( getmonthId int NOT NULL, ProductName varchar(50) NOT NULL, getmonthDate datetime NOT NULL DEFAULT NOW(), PRIMARY KEY (getmonthId) )

edited Jul 01 '15 at 07:18

answered Jul 01 '15 at 06:53

Vinay Guru

79
1
6
19

score 0 · Answer 3 · answered Jul 01 '15 at 06:59

For SQL Server 2005, you can use combination of DATEADD and DATEPART. You can easily collapse it to single line of code, but this makes for easier readability.

declare @startdate datetime
  , @week int
  , @enddate datetime
  , @mo int

select @startdate = '12/28/2014', @week = 23
select @enddate = dateadd(wk, @week, @startdate)
select @mo = datepart(m, @enddate)

select @mo, @enddate

How to get month number in sql by passing the week number

3 Answers3