July 6, 2012 at 11:59 am
For my business, it begins on the first day of October and ends on the last day of the following September.
July 6, 2012 at 12:18 pm
tim.cloud (7/6/2012)
For my business, it begins on the first day of October and ends on the last day of the following September.
The follow snippet should work for you. Simply replace GETDATE() with the actual date column (or date parameter) as needed.
DECLARE @testdate DATETIME;
SET @testdate = GETDATE();
SELECT @testdate,DATEADD(mm, -3,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 3, @testdate)) + 1, -1))
SET @testdate = '20120929';
SELECT @testdate,DATEADD(mm, -3,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 3, @testdate)) + 1, -1))
SET @testdate = '20121029';
SELECT @testdate,DATEADD(mm, -3,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 3, @testdate)) + 1, -1))
July 6, 2012 at 12:24 pm
tim.cloud (7/6/2012)
For my business, it begins on the first day of October and ends on the last day of the following September.
Well the last day of September is always the same each year too. Just modify the other code snippets and change the month to 9 and the day to 30.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 12:27 pm
Code has been replaced and I included some test data with it.
July 6, 2012 at 12:31 pm
tim.cloud (7/6/2012)
For my business, it begins on the first day of October and ends on the last day of the following September.
In addition to what everyone else has already added, don't forget to account for whether your fiscal year ends on a business day or a calendar day.
July 6, 2012 at 12:37 pm
Brandie Tarvin (7/6/2012)
tim.cloud (7/6/2012)
For my business, it begins on the first day of October and ends on the last day of the following September.In addition to what everyone else has already added, don't forget to account for whether your fiscal year ends on a business day or a calendar day.
True, you can get some really interesting fiscal years. One company I worked for the fiscal year started on the first Sunday of October and ended the Saturday before the first Sunday of October in the following year. Fiscal months followed the same pattern using a 4-4-5 week schema with the occasional 4th quarter actually being a 4-4-6.
July 6, 2012 at 12:59 pm
Thanks. That code works great. We are going to start table partitioning all our data into yearly partitions as well.
July 6, 2012 at 3:37 pm
Brandie Tarvin (7/6/2012)
I've never run into an instance where using CAST over CONVERT (or vice versa) has made a difference in code performance. The problem is usually related to the rest of the code, not the choice of conversion function.
I would say that it's the choice of using a conversion function or not.
--Jeff Moden
July 6, 2012 at 3:40 pm
Not sure if this is what you need or not but this prodedure assumes that your fiscal year begins on October 1 and will provide the last date of the fiscal year a given date fall in:
create procedure sp_lastdayfiscalyear (@Date Date) as
If DATEPART(mm,@Date) < 9
Begin
select Convert(date,Convert(varchar(4),DATEPART(yyyy,@Date))+'-9-30')
End
Else
Select Convert(date,DateAdd(yyyy,1,Convert(varchar(4),DATEPART(yyyy,@Date))+'-9-30'))
So
exec sp_lastdayfiscalyear '2012-08-15'
Returns
(No column name)
2012-09-30
and
exec sp_lastdayfiscalyear '2012-10-15'
Returns
(No column name)
2013-09-30
July 6, 2012 at 3:48 pm
dan-572483 (7/6/2012)
Not sure if this is what you need or not but this prodedure assumes that your fiscal year begins on October 1 and will provide the last date of the fiscal year a given date fall in:
create procedure sp_lastdayfiscalyear (@Date Date) asIf DATEPART(mm,@Date) < 9
Begin
select Convert(date,Convert(varchar(4),DATEPART(yyyy,@Date))+'-9-30')
End
Else
Select Convert(date,DateAdd(yyyy,1,Convert(varchar(4),DATEPART(yyyy,@Date))+'-9-30'))
So
exec sp_lastdayfiscalyear '2012-08-15'Returns
(No column name)2012-09-30
and
exec sp_lastdayfiscalyear '2012-10-15'Returns
(No column name)2013-09-30
Please check out the code I posted. You will find it more efficient than your and doesn't require all the conversions to and from character strings which will not scale well.
July 6, 2012 at 4:09 pm
In Estimated Execution Plan I'm showing 0 cost for both methods, and Client Statistics shows both methods with virtually the same Client Execution Time.
How do you determine which is more efficient?
July 6, 2012 at 4:16 pm
dan-572483 (7/6/2012)
In Estimated Execution Plan I'm showing 0 cost for both methods, and Client Statistics shows both methods with virtually the same Client Execution Time.How do you determine which is more efficient?
Actually, if you run my original code, you should find that your string conversion is slower.
Please check your procedure, by the way, I think you will find a simple logic error in the code because if you pass it the date '2012-09-15' you will get '2013-09-30' instead of '2012-09-30'.
Also, I think I found a more efficient way to encapsulate your string conversion routine:
Convert(date,convert(varchar(4),CASE WHEN MONTH(MyYear) < 10 THEN YEAR(MyYear) ELSE YEAR(MyYear) + 1 END)+'-09-30')
Then, I modified my test code to use this and was surprised by the results. All three appear to be equivalent.
Full result sets:
SET NOCOUNT ON;
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101' ) AS MyYear FROM tally)
SELECT
MyYear, CAST(CAST(CASE WHEN MONTH(MyYear) < 10 THEN YEAR(MyYear) ELSE YEAR(MyYear) + 1 END AS CHAR(4)) + '-09-30' AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101') AS MyYear FROM tally)
SELECT
MyYear, Convert(date,convert(varchar(4),CASE WHEN MONTH(MyYear) < 10 THEN YEAR(MyYear) ELSE YEAR(MyYear) + 1 END)+'-09-30')
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101' ) AS MyYear FROM tally)
SELECT
MyYear, CAST(DATEADD(mm, -3,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 3, MyYear)) + 1, -1)) AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT OFF;
Dumping to a bit bucket:
SET NOCOUNT ON;
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101' ) AS MyYear FROM tally)
SELECT
@MyDate = CAST(CAST(CASE WHEN MONTH(MyYear) < 10 THEN YEAR(MyYear) ELSE YEAR(MyYear) + 1 END AS CHAR(4)) + '-09-30' AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101') AS MyYear FROM tally)
SELECT
@MyDate = Convert(date,convert(varchar(4),CASE WHEN MONTH(MyYear) < 10 THEN YEAR(MyYear) ELSE YEAR(MyYear) + 1 END)+'-09-30')
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101' ) AS MyYear FROM tally)
SELECT
@MyDate = DATEADD(mm, -3,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 3, MyYear)) + 1, -1))
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT OFF;
July 6, 2012 at 4:27 pm
You're right - should have been <= 9 or < 10.
July 6, 2012 at 4:39 pm
I found a copy/paste error with my code. Fixed it above, but it didn't really change much.
July 6, 2012 at 4:44 pm
dan-572483 (7/6/2012)
You're right - should have been <= 9 or < 10.
There is one advantage of my date calc routine, if the fiscal year ends at the end of February mine doesn't need to test if it is a leap year.
SET NOCOUNT ON;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT DATEADD(dd, FLOOR(3652 * rand(CHECKSUM(NEWID()))), '20000101' ) AS MyYear FROM tally)
SELECT
MyYear, CAST(DATEADD(mm, -10,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(mm, 10, MyYear)) + 1, -1)) AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT OFF;
GO
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply