how can i achieve that using this this query. 38 You can use TRUNC function to truncate date to the first day of week. When i run this query ,at the end of the 2013 it gives the result as,įor dates 29 ,30,31st it should give 53. This query gives date, weekday and week_num for 2 years 20, To_char(next_day(mydate,'sunday'),'iw') as week_numįROM ( SELECT TRUNC (SYSDATE, 'yy') - 1 + LEVEL AS mydateĬONNECT BY LEVEL <= (SELECT TRUNC (ADD_MONTHS (SYSDATE, 24), 'yy') I have a query that starts with 01 when year starts but it gives problem in the end of the year. SET DATEFIRST 1 - Set the first day of the week to Monday select dbo.FiscalWeek ('04',getdate ()) The '04' parameter is because in my experience April is the most common starting month, but you can pass in '01' to get ISO8601 week numbers, or whichever month number suits your needs. How can i achieve this, can anyone please help me out on this. again in the next year it should start with '01'. DECLARE date date '' SELECT DATEPART(week, date. To return the week number, use week as the first argument of the DATEPART() function. By week number I mean the week’s number within the year of the specified date. in the end date of the year it should not be 'saturday' but week number should end with last date of the year. You can use the T-SQL function DATEPART() to return the week number from a date in SQL Server. so next week number starts with sunday and ends with saturday ,continously. So that week number should start with 01 when the year starts and it should end with week end date(that is first saturday of the january month). To find the week number of a given date adate, you can use this formula: 1 + FLOOR ( (adate - fiscalyearbegin (adate)) / 7 ) which finds how may days have passed since the beginning of the fiscal year, dvides by 7 to get the number of completed weeks (0-52), and adds 1 to get the number of the week in progress. I have the requirement to find the week number for the calender. 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.
0 Comments
Leave a Reply. |