CalendarQuarterDateRange

 CREATE PROCEDURE [dbo].[CalendarQuarterDateRange]

AS
begin
declare 
  @today date
, @todayquarter int
, @reportyear int
, @reportquarter int
, @quarterbegindate datetime
, @quarterenddate datetime;

set @today = getdate();
set @todayquarter = datepart(quarter, @today);

--
-- figure out the reportyear and reportquarter
--
if @todayquarter = 1
begin
set @reportyear = datepart(year, @today) - 1;
set @reportquarter = 4;
end
else 
begin
set @reportyear = datepart(year, @today);
set @reportquarter = @todayquarter - 1;
end

--
-- figure out the begin and end date for the reportquarter
--
if @reportquarter = 1
begin
set @quarterbegindate = datefromparts(@reportyear, 1, 1);
set @quarterenddate = datefromparts(@reportyear, 3, 31);
end
else if @reportquarter = 2
begin
set @quarterbegindate = datefromparts(@reportyear, 4, 1);
set @quarterenddate = datefromparts(@reportyear, 6, 30);
end
else if @reportquarter = 3
begin
set @quarterbegindate = datefromparts(@reportyear, 7, 1);
set @quarterenddate = datefromparts(@reportyear, 9, 30);
end
else if @reportquarter = 4
begin
set @quarterbegindate = datefromparts(@reportyear, 10, 1);
set @quarterenddate = datefromparts(@reportyear, 12, 31);
end

select 
  @quarterbegindate quarterbegindate
, @quarterenddate quarterenddate;

end

Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more