T-SQL Trick: How to Find Monday (or Any Day) of the Current Week

Ever needed to filter a report so it only shows data starting from the beginning of the current week? In SQL Server, calculating the date for "this past Monday" is a classic challenge that every dev faces eventually.

While there are many ways to do it, the most efficient method uses a clever combination of DATEDIFF and DATEADD.

The Logic: How It Works

The secret sauce is calculating the number of weeks that have passed since "Day 0" (which is 1900-01-01, a Monday) and then adding those weeks back to that same starting point.

Here is the cleanest way to write it:

-- Using the 'WK' shorthand

SELECT DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0) AS MondayOfCurrentWeek;

-- Or using the full 'WEEK' keyword for readability

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS MondayOfCurrentWeek;

Both queries will return the exact date of Monday for the current week (e.g., 2026-04-13).

Getting Other Days of the Week

The beauty of this snippet is its flexibility. If you need to find Tuesday, Wednesday, or any other day, you don't need to rewrite the logic—you just need to shift the "anchor" value (the 0 in the formula).

By incrementing the integer, you move the target day forward:

-- Tuesday

SELECT DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 1) AS Tuesday;

-- Wednesday

SELECT DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 2) AS Wednesday;

-- Thursday

SELECT DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 3) AS Thursday;

Why use this instead of DATEPART?

The main advantage here is that this method is deterministic and ignores your server's DATEFIRST settings. Whether your server thinks the week starts on Sunday or Monday, this math always anchors back to that original Monday in 1900, keeping your reports consistent across different environments.

SYS.SQL_MODULES in T-SQL (Catalog View)

The sys.sql_modules system view is a powerful tool for returning object details within a SQL Server database. It allows developers to easily retrieve the definition and metadata for various objects, including Stored Procedures, Replication-filter procedures, Views, and DML Triggers. Additionally, it covers SQL Scalar Functions, Inline Table-valued Functions, Table-valued Functions, and Rules. Using this view is an essential way to audit or search your codebase programmatically.

Columns

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

SELECT  object_id, definition, uses_ansi_nulls, uses_quoted_identifier,
                 is_schema_bound,uses_database_collation, is_recompiled,null_on_null_input,                                      execute_as_principal_id
 FROM   sys.sql_modules

Logical processing order of SELECT statement

When a SQL query executes on SQL Server, it follows a specific sequence known as the Logical Query Processing Order. While we write queries starting with SELECT, SQL Server processes them in a very different order to ensure data is filtered and grouped correctly before being returned. Understanding this flow is essential for writing efficient code and troubleshooting unexpected results. Let’s break down the logical order of a SELECT statement.

1. FROM
2. ON
3. OUTER
4. WHERE
5 GROUP BY
6 CUBE or ROLLUP
7 HAVING
8 SELECT
9 DISTINCT
10 ORDER BY
11 TOP

SQL query to get table names and schema in Database

There are several ways to retrieve table names and their associated schemas within a SQL Server database. Depending on your specific needs—whether you are automating a script or performing a quick manual check—different methods offer different levels of detail. Below, I have outlined some of the most common and effective ways to query your database metadata.

1.
SELECT SCHEMA_NAME(schema_id) 'SchemaName' , name 'TableInDb' 
FROM sys.tables

Return all tables and related schema in selected database



2.
SELECT TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,TABLE_TYPE  FROM INFORMATION_SCHEMA.TABLES

this will return more information than first one, it's return VIEWs in Database as well



3.
  SELECT t.name 'TableName' ,s.name 'SchemaName'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]

  SELECT t.name 'TableName' ,s.name 'SchemaName'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo';

UNION and UNION ALL in T-SQL

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. To use it successfully, you must follow three key rules:

• Column Consistency: Each SELECT statement must have the same number of columns.

• Data Compatibility: The corresponding columns in each statement must have similar or compatible data types.

• Distinct Results: By default, UNION removes duplicate records and returns only distinct values.

UNION ALL

The UNION ALL operator works similarly to UNION, but with one major difference: it retrieves all rows from the selected tables without filtering for duplicates. Because it doesn't spend resources searching for and removing identical records, UNION ALL is significantly faster and more efficient when you know your data is unique or when you specifically want to include every result.  

Use UNION ALL by default if your data is already unique or if you don't mind including duplicate records. Because UNION must perform extra work to ensure only distinct values are returned, it is slower than UNION ALL. Only reach for the standard UNION when you specifically need to filter out overlapping data between your query results.

5 Ways to Master Dynamic WHERE Clauses in T-SQL

Stop struggling with complex search filters in your stored procedures. This guide breaks down the most effective techniques for building dynamic WHERE clauses in SQL Server.

• COALESCE
• OR Operator
• ISNULL
• CASE
• Dynamic SQL query.

Note - All example I have done with NORTHWIND database

1 . COALESCE.

DECLARE @OrderID int,
                   @ProductID int


SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
FROM [dbo].[OrderDetails]
WHERE OrderID = COALESCE(@OrderID , OrderID)
               AND [ProductID] =COALESCE(@ProductID , [ProductID])

   Find out more about COALESCE

2. OR Operator.

DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
                 ,[ProductID]
                 ,[UnitPrice]
                 ,[Quantity]
                 ,[Discount]
FROM    [dbo].[OrderDetails]
WHERE  ((@OrderID IS NULL) OR (OrderID = @OrderID))
                 AND ((@ProductID IS NULL) OR (ProductID =@ProductID))

3. ISNULL.

DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
 FROM   [dbo].[OrderDetails]
 WHERE OrderID = ISNULL(@OrderID , OrderID)
                AND [ProductID] =ISNULL(@ProductID , [ProductID])

Find out more about ISNULL

4. CASE

 DECLARE @OrderID int,
                    @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
             ,[ProductID]
             ,[UnitPrice]
             ,[Quantity]
             ,[Discount]
 FROM [dbo].[OrderDetails]
 WHERE OrderID = CASE WHEN @OrderID IS NOT NULL THEN @OrderID ELSE OrderID                                          END
                                  AND [ProductID] =CASE WHEN @ProductID IS NOT NULL THEN                                                 @ProductID ELSE ProductID END

5. Dynamic SQL Query

DECLARE @OrderID int,
                   @ProductID int ,
                   @DyWhere nvarchar(MAX),
                   @DyQuery nvarchar(MAX)

SET @OrderID =10249
SET @ProductID =NULL
SET @DyWhere =' WHERE'

SET @DyQuery ='SELECT [OrderID]
                                             ,[ProductID]
                                             ,[UnitPrice]
                                             ,[Quantity]
                                             ,[Discount]
                                              FROM [dbo].[OrderDetails]'


IF @OrderID IS NOT NULL
BEGIN
   SET @DyWhere =@DyWhere+ ' OrderID ='+CONVERT(nvarchar, @OrderID)
 
END

IF @ProductID IS NOT NULL
BEGIN
SET @DyWhere =@DyWhere + ' AND ProductID ='+CONVERT(nvarchar, @ProductID)
END

IF @OrderID IS NOT NULL OR @ProductID IS NOT NULL
BEGIN
SET @DyQuery =@DyQuery +@DyWhere
END


EXECUTE sp_ExecuteSQL @DyQuery

Find out more about  sp_ExecuteSQL

You can use above mentioned one of method according to your scenario , Last one (dynamic SQL query ) this can use for more dynamic scenarios.

Concatenating row values from table to single string using T-SQL

There are several ways to concatenate row values into a single string in SQL Server, including using custom SQL functions, COALESCE, or the FOR XML PATH clause. In this post, I will demonstrate how to perform string aggregation using XML PATH and COALESCE. To make these examples easy to follow, I'll be using the NORTHWND sample database to show these methods in action.

Example 1 : COALESCE (find more about COALESCE)

Using COALESCE function , we can concatenate many row values in to one single string. this is one use of COALESCE , there are many ,so you can find some more clicking above mention link.

Example 2: XML PATH

 1


2

To ensure our final string doesn't start with an extra comma, we use the STUFF function. STUFF works by deleting a specific portion of a string and replacing it with another. In this case, we use it to target the very first character—the extra comma—and replace it with nothing, effectively removing it from the beginning of our result.

Common Table Expression (CTE) in Sql Server

Common Table Expressions, or CTEs, can be thought of as temporary views that are created in memory for a single execution scope. They allow you to simplify complex joins and subqueries by defining a named result set that exists only until the next query runs. Whether you are using Recursive or Non-Recursive CTEs, understanding their limitations—such as being unindexable and requiring immediate execution—is key to writing efficient SQL code.

• We can take CTE as tempory view (can be thought as tempory result set) that create in memory

• Its scope valid until next query run

• CTE need to use just after it declaration  

• There are two type Recursive and Nonrecursive CTE

• Can define multiple CTE

• CTE are unindexable ( it cau use exist index in referenced object)

• CTE can't have constrain

• ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword

Simple Example.

ex 1

WITH CTE_CUSTOMER_EX2
(         [CustomerID]
          ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]
)
AS
(
SELECT [CustomerID]
  ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]

  FROM .[dbo].[Customers]
)


SELECT * FROM CTE_CUSTOMER_EX2

ex 2

;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
 ,[CompanyName]
 ,[ContactName]
 FROM .[dbo].[Customers]

)
SELECT * FROM CTE_CUSTOMER_EX1

Note
If there are query before CTE define , need to use Semicolon teminator before declare CTE


Multiple CTE

  With CTE_M1 as 
(
-select statement
) 
     , CTE_M2 as 
(
-select statemet

)

you can find some more detail and examples using this Link and Link2