how to get week number for a date based on month from the date in sql server

-
 
We have a date column in a table and I want to get week number for that particular date based on the month from that date irrespective of the day

For example:
01-dec-2012 to 07-dec-2012 should give week number as 108-dec-2012 to 14-dec-2012 should give week number as 215-dec-2012 to 21-dec-2012 should give week number as 3
 
 
You can use DAY (Transact-SQL)  
 
select ((day(ColumnName)-1) / 7) + 1 as W
 
Example :
 
select ((day('2012-11-01 00:00:00.000')-1) / 7) + 1 as W
 
Output :
 
W
--
1

Comments

Post a Comment

Popular posts from this blog

SQL – Generate decimal numbers sequence for particular number range

-
Image
Friends, Sometimes we need mathematical assistance in SQL for such queries. Same here, it helps me to find challenging in all the way. Some of my friends helps me out to crack the records for my query, Here I am sharing simple solution for this, ;WITH cte      AS (SELECT Cast(-77 AS NUMERIC(22, 3)) AS num –Min value from your data          UNION ALL          SELECT Cast(num + 0.00001 AS NUMERIC(22, 5))          FROM   cte          WHERE  num < Cast(89 AS NUMERIC(22, 5))) — Max value from your data SELECT * FROM   cte OPTION (maxrecursion 0)  Try it 
Read more

File operations in C# (.net)

-
Here we are performing some basic file operations which widely needed in applications. ASPX :     <div>         <asp:Button ID="Button1" runat="server" Text="CLick" onclick="Button1_Click" />         <asp:FileUpload ID="FileUpload1" runat="server" /><br /><br />          </div> C# :        using System.IO;        using System.Text;         protected void Button1_Click(object sender, EventArgs e)         {             string path = @"E:\MVC Application Demos\ABC.txt";            // File Path taken             string name = FileUpload1.FileName.ToString();                // Here we get File Name only             string pa...
Read more

Error Message Severity Levels in SQL Server

-
Sample Code: BEGIN TRY     SELECT 1/0; END TRY BEGIN CATCH     SELECT ERROR_SEVERITY() AS ErrorSeverity; END CATCH; GO Output : 16 ############################################################### Here we didnt understand what exactly severity occurs in SQL Expression Find descriptions here for each severity level. Severity Levels 0 through 19 : Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by the user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted. Severity Level 10: Status Information This is an informational message that indicates a problem caused by mistakes in the information the user has entered. Severity level 0 is not visible in SQL Server. Severity Levels 11 through 16 These messages indicate errors that can be corrected by the user. Severity Le...
Read more