locked
Year, Week and Weekdate in SQL RRS feed

  • Question

  • User1152553138 posted
    Year, Week and Weekdate in SQL
    
    Year dropdown ,Week dropdown and Weekdate dropdown
    
    Year will be available in dropdown list
    Say 2016,2017
    
    Whenever year is selected all the week number of that particular year should be displayed in Week dropdownlist
    Say  Week Number 29
    
    When Week number is selected all Weekdate should be displayed in dropdown list 
    Say  11-July-2016 12-July-2016 13-July-2016 14-July-2016 15-July-2016 16-July-2016
    
    DECLARE @DATE TABLE(D DATETIME)
    DECLARE @D DATETIME
    --Set the Start Date
    SET @D=CONVERT(VARCHAR(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()) - 5, GETDATE())), 101)
    --Loop through week and add the entries to temp table
    WHILE @D<=CONVERT(VARCHAR(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()), GETDATE())), 101)
    BEGIN
    	INSERT INTO @DATE VALUES (@D)
    	SET @D=@D+1
    END
    --Return the result
    SELECT RIGHT('0' + DATENAME(DAY, D), 2) + '-' + DATENAME(MONTH, D)+ '-' + DATENAME(YEAR, D) AS DATECOL,
    DATENAME(ww, GETDATE()) as WeekNo FROM @DATE  

    In the above query all date of week number will be displayed. Note Week start on Monday and End on Saturday

    Note:-

    I just need SQL Query for Year, Week Number and Week Date of given Week Number

    Tuesday, July 12, 2016 6:06 AM

Answers

All replies

  • User1724605321 posted

    Hi  Ashraf007 ,

    I just need SQL Query for Year, Week Number and Week Date of given Week Number

    You could refer to below thread for solution :

    1.Get all Week numbers by a given Year

    2. Get dates from a week number in T-SQL

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2016 6:46 AM
  • User1152553138 posted

    Hi  Ashraf007 ,

    Ashraf007

    I just need SQL Query for Year, Week Number and Week Date of given Week Number

    You could refer to below thread for solution :

    1.Get all Week numbers by a given Year

    2. Get dates from a week number in T-SQL

    Best Regards,

    Nan Yu

    I need only sql query

    1. I will pass getdate() in select query from that i need to only yyyy that year only.

    Eg : yyyy  as 2016

    2. Then when i pass yyyy that is year as 2016 all week number of 2016 should be displayed. Consider start day as Monday and end as Saturday.

    Eg: ww as 29 that is current date week number

    3. Then when i pass week number all date between that particular week should be displayed.

    Eg: Date of week no 29, 11-Jul-2016,12-Jul-2016,13-Jul-2016,14-Jul-2016,15-Jul-2016,16-Jul-2016 (i.e) Monday to Saturday

    I hope its clear now.

    Wednesday, July 13, 2016 9:34 AM
  • User-718146471 posted

    Ok, so here is what you can do.

    SELECT DATEPART(YEAR, getdate()) as YEAR, DATEPART(wk, getdate()) as WeekNum, DATEPART(dw,GETDATE()) as WeekDayNum, 
    DATENAME(dw,GETDATE()) as DayName GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2016 11:37 AM
  • User-718146471 posted

    So if you are passing a date into the query, simply declare that variable and pass the value into it. I used getdate() as placeholder. You can use whatever date you want.

    Wednesday, July 13, 2016 11:42 AM
  • User-718146471 posted

    And the result:

    Year	WeekNum		WeekDayNum	DayName
    2016	29		4		Wednesday

    Wednesday, July 13, 2016 11:44 AM
  • User-718146471 posted

    Lastly, using variable name to specify your own date:

    DECLARE @MyDate datetime
    set @MyDate = '06/01/2016'
    SELECT DATEPART(YEAR, @MyDate) as Year, DATEPART(wk, @MyDate) as WeekNum, 
    DATEPART(dw,@MyDate) as WeekDayNum, DATENAME(dw,@MyDate) as DayName

    Wednesday, July 13, 2016 11:47 AM
  • User1152553138 posted

    Lastly, using variable name to specify your own date:

    DECLARE @MyDate datetime
    set @MyDate = '06/01/2016'
    SELECT DATEPART(YEAR, @MyDate) as Year, DATEPART(wk, @MyDate) as WeekNum, 
    DATEPART(dw,@MyDate) as WeekDayNum, DATENAME(dw,@MyDate) as DayName

    hello thanks ..

    I need all week of the year 2016. Say week 01,02,03,04 up to 52 or 53 week number

    Then when i pass week number say 29 then all date between the week no 29 should be displayed like 11-Jul-2016,12-Jul-2016,13-Jul-2016,14-Jul-2016,15-Jul-2016,16-Jul-2016 (i.e) Consider my week start from Monday to Saturday

    Wednesday, July 13, 2016 12:06 PM
  • User-718146471 posted

    Oh, so you need a list of all the weeks?

    And you are looking to display the dates for all days in that week selected? 

    Do you have an example of how you want it to look so I know what you are looking for specifically?

    Wednesday, July 13, 2016 12:37 PM
  • User-718146471 posted

    Never mind, I figured out what you are trying to do. You want a program that lists all the weeks and then you want to pass this into SQL to get the dates of the week specified. Stop me if I am incorrect but this sounds like a homework assignment, am I right?

    Wednesday, July 13, 2016 12:39 PM
  • User1152553138 posted

    Never mind, I figured out what you are trying to do. You want a program that lists all the weeks and then you want to pass this into SQL to get the dates of the week specified. Stop me if I am incorrect but this sounds like a homework assignment, am I right?

    Exactly. Now you got me ...!

    Thursday, July 14, 2016 3:16 AM
  • User-62323503 posted

    Refer below post:

    http://www.itdeveloperzone.com/2015/10/generating-week-dates-for-year-in-sql.html

    This will help your to generate all weeks with start and date for each week for a given year.

    You can then use recursive cte between start date and end date for each week.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 14, 2016 7:48 AM
  • User1152553138 posted

    Refer below post:

    http://www.itdeveloperzone.com/2015/10/generating-week-dates-for-year-in-sql.html

    This will help your to generate all weeks with start and date for each week for a given year.

    You can then use recursive cte between start date and end date for each week.

    Above link is perfect to get weekno of full year

    DECLARE @DATE TABLE(D DATETIME)
    DECLARE @D DATETIME
    --Set the Start Date
    SET @D=CONVERT(VARCHAR(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()) - 5, GETDATE())), 101)
    --Loop through week and add the entries to temp table
    WHILE @D<=CONVERT(VARCHAR(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()), GETDATE())), 101)
    BEGIN
    	INSERT INTO @DATE VALUES (@D)
    	SET @D=@D+1
    END
    --Return the result
    SELECT RIGHT('0' + DATENAME(DAY, D), 2) + '-' + DATENAME(MONTH, D)+ '-' + DATENAME(YEAR, D) AS DATECOL,
    DATENAME(ww, GETDATE()) as WeekNo FROM @DATE
    
    
    Here i have passed getdate()?
    Can i pass week number in the above query?

    Thursday, July 14, 2016 9:25 AM
  • User-718146471 posted

    Ok, so if you are using SQL Server for example, there really is no way for you to get user input according to http://stackoverflow.com/questions/11974873/sql-user-input-in-query so you will need some program using C# or other programming language to get that input. Unless you use some type of command line parameter where the program will make a choice. First run shows all weeks, then if you specify the parameter of the week number it will display the dates of the days of that week of the year. Let us know if that is what you are trying to do.

    Friday, July 15, 2016 11:37 AM
  • User1152553138 posted

    Ok, so if you are using SQL Server for example, there really is no way for you to get user input according to http://stackoverflow.com/questions/11974873/sql-user-input-in-query so you will need some program using C# or other programming language to get that input. Unless you use some type of command line parameter where the program will make a choice. First run shows all weeks, then if you specify the parameter of the week number it will display the dates of the days of that week of the year. Let us know if that is what you are trying to do.

    Yes ...  Exactly

    Friday, July 15, 2016 12:08 PM
  • User-718146471 posted

    Ok, give us some time and we will try to put something together for you.

    Friday, July 15, 2016 1:36 PM
  • User-718146471 posted

    Did you ever figure this out on your own? I think we all gave you the tools you need to solve this problem.

    Thursday, July 28, 2016 12:29 PM