March 10, 2017 at 8:44 am
Hi,
I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.
So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.
I've got this far:
WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())
...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.
I'm new to SQL so all help welcome - please be gentle 🙂
March 10, 2017 at 9:03 am
WHERE CONVERT(date, t1.transactiondate) >= DATEADD(month,DATEDIFF(month,'19800101',GETDATE())-12,'19800101')
AND CONVERT(date, t1.transactiondate) < DATEADD(month,DATEDIFF(month,'19800101',GETDATE()),'19800101')
John
March 10, 2017 at 9:06 am
john.dixon 58151 - Friday, March 10, 2017 8:44 AMHi,I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.
So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.
I've got this far:
WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.
I'm new to SQL so all help welcome - please be gentle 🙂
You can accomplish by doing some date math. In my example I am using 0 as a date. In sql server this will implicitly become 1900-01-01. Here is an example of getting the beginning of the current month last year and the last day of the current month.
select BeginningOfThisMonthLastYear = dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0))
, EndOfThisMonth = dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
_______________________________________________________________
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/
March 10, 2017 at 9:14 am
Maybe something like this. Generally, I use a function to normalize all date ranges for all reports.
SELECT
CONVERT(varchar(6),DATEADD(MONTH,-12,GETDATE()),112)+'01',
CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),GETDATE()),112)
;
March 10, 2017 at 9:33 am
Bill Talada - Friday, March 10, 2017 9:14 AMMaybe something like this. Generally, I use a function to normalize all date ranges for all reports.
SELECT
CONVERT(varchar(6),DATEADD(MONTH,-12,GETDATE()),112)+'01',
CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),GETDATE()),112)
;
Careful with that. Character conversions don't perform as well as date arithmetic. I ran the two batches below several times against a table with nearly 4000 objects. The first query had CPU and elapsed times around 14 seconds, the second between two and three seconds.set STATISTICS time on
DECLARE @t table (d1 date, d2 date)
insert into @t
select CONVERT(varchar(6),DATEADD(MONTH,-12,o1.create_date),112)+'01' AS d1,
CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),o1.modify_date),112) AS d2
from sys.objects o1 cross join sys.objects o
go
DECLARE @t table (d1 date, d2 date)
insert into @t
select DATEADD(month,DATEDIFF(month,'19800101',GETDATE())-12,'19800101') AS d1,
DATEADD(month,DATEDIFF(month,'19800101',GETDATE()),'19800101') AS d2
from sys.objects o1 cross join sys.objects o
GO
John
March 10, 2017 at 9:37 am
Sean Lange - Friday, March 10, 2017 9:06 AMjohn.dixon 58151 - Friday, March 10, 2017 8:44 AMHi,I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.
So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.
I've got this far:
WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.
I'm new to SQL so all help welcome - please be gentle 🙂
You can accomplish by doing some date math. In my example I am using 0 as a date. In sql server this will implicitly become 1900-01-01. Here is an example of getting the beginning of the current month last year and the last day of the current month.
select BeginningOfThisMonthLastYear = dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0))
, EndOfThisMonth = dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
Wow, thanks all for quick and helpful responses. I'm going with Sean's solution, I just need a quiet moment to unpick exactly how the datediff and dateadd arguments are combining! Thanks again everyone 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply