November 20, 2015 at 8:38 am
Phil Parkin (11/20/2015)
(snip)My preference is to make the end date 'midnight on the day after the final day of the period' and then use < in any comparisons.
For example, for 2015
StartDate = '20150101'
EndDate = '20160101'
select from table
where date >= StartDate and date < EndDate
Works with all date data types and keeps things clean.
Understood; I tried to keep things simpler so that one could use the end date in a header to display a date range, without having to do date arithmetic in a SSRS formula. My preference is to keep the date logic in the SQL layer. Of course, one could add another column to the view for Display Ending date.
Thanks
John.
November 20, 2015 at 10:09 am
Excellent concept, I'll be using it. It's modifiable to use any date format the user needs/wants. I haven't checked the logic for all the time ranges, but the fiscal year needs some work, because it tends to cross year boundaries. It returns an interesting date range if the fiscal year start month is after the current month.
Thanks.
I Just read an earlier post. Didn't mean to repeat.
November 21, 2015 at 5:20 pm
Good concept and idea. I had implemented something similar. I am sure we have all seen the choose a month to report from . In these cases I instructions in the label to input month as '2010/01' or the like. I worked on the string to create the data range.
Do you have a separate report that allows a variable date range (to check numbers on a particular date for example) or do you run this for the user upon request? I am interested in how others have handled this.
Thanks
----------------------------------------------------
November 23, 2015 at 2:04 pm
Thanks for the great article.
November 24, 2015 at 2:55 pm
MMartin1 (11/21/2015)
Good concept and idea. I had implemented something similar. I am sure we have all seen the choose a month to report from . In these cases I instructions in the label to input month as '2010/01' or the like. I worked on the string to create the data range.Do you have a separate report that allows a variable date range (to check numbers on a particular date for example) or do you run this for the user upon request? I am interested in how others have handled this.
I was thinking about this as well: what if they wanted to "usually" pick a range like this, but occasionally fine-tune the date range? Best workaround I've come up with so far with our limited abilities in SSRS parameters (and I'd love to hear a better one):
(1) Add a fourth field, let's say "Range", to the view, containing both the BeginDate and EndDate. It'll have to be in char format. So if BeginDate='2015-01-01' and EndDate=' 2015-01-31', Range='2015-01-01 to 2015-01-31' or something.
(2) On your report, the first parameter is named DateRange and it uses this view as the data source. Value field is Range, Label field is still Period.
(3) Next parameter is StartDate, default value is: =CDate(Left(Parameters!DateRange.Value, 10))
(4) Next parameter is EndDate, default value is: =CDate(Right(Parameters!DateRange.Value, 10))
(5) Now you just use StartDate and EndDate as your date range. In your descriptive report header, do NOT use the Period text since it could be misleading; just put in the actual date range based on StartDate and EndDate.
This isn't pretty because parameters StartDate and EndDate won't refresh if they select something from parameter DateRange then change your mind and select something else. It's not as intuitive as I'd like. But it's the best hack I can come up with. (I also thought about adding a "use range / select dates freely" toggle, or a "wrapper" report that let you pick a range but passed you to a a report with free text choices... all even uglier, I think.) Maintaining two copies of a report that are identical save the parameters is something I'd want to avoid if possible.
March 31, 2017 at 1:39 am
Great idea, thanks for sharing it.
March 31, 2017 at 3:03 am
It's a great idea. When implementing it though, be wary when retrieving results for date ranges. Take the example for yesterday:select
Convert(varchar(100),'Yesterday') as [Period],
convert(datetime,convert(date,getdate()))-1 as BeginDate,
convert(datetime,convert(date,getdate()))-1 as EndDate
It will return a BeginDate of 2017-03-30 00:00:00.000 and an end date of 2017-03-30 00:00:00.000
If you're comparing with a datetime field, you may not get all the results that you expect
**edit**
unless you join back to the table in the way specified:and [ending Date] < DR.EndDate+1
March 31, 2017 at 8:55 am
Very handy - Thank you! I made one minor change to provide the correct year for the BeginDate of the fiscal year to date:
select
Convert(varchar(100),'Fiscal Year To Date') as [Period]
,CASE when month(getdate()) < 10
THEN convert(datetime,'10/1/' + cast(year(getdate())-1 as varchar(4))) -- the previous calendar year
ELSE convert(datetime,'10/1/' + cast(year(getdate()) as varchar(4))) -- the current calendar year
END as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
March 31, 2017 at 12:55 pm
My biggest suggestions are these:
1) Eliminate the use of character conversions. There are examples out there that show how to do a variety of the date manipulations being done with character conversions in this code using the datetime functions.
2) When using dateparts use the full name not the short cuts, makes it more understandable to others reading the code.
3) Even though you can use getdate() - 1 to subtract one day, use the date function dateadd to add or subtract days, months, years, quarters, seconds, etc. Makes the code more understandable to less experienced individuals.
March 31, 2017 at 12:59 pm
laurencemadill - Friday, March 31, 2017 3:03 AMIt's a great idea. When implementing it though, be wary when retrieving results for date ranges. Take the example for yesterday:select
Convert(varchar(100),'Yesterday') as [Period],
convert(datetime,convert(date,getdate()))-1 as BeginDate,
convert(datetime,convert(date,getdate()))-1 as EndDateIt will return a BeginDate of 2017-03-30 00:00:00.000 and an end date of 2017-03-30 00:00:00.000
If you're comparing with a datetime field, you may not get all the results that you expect
**edit**
unless you join back to the table in the way specified:and [ending Date] < DR.EndDate+1
Hi - you are correct and that is mentioned in the code snippet in the article; it was done to make it easier to display the end date.
Thanks
John.
March 31, 2017 at 1:00 pm
sisillip - Friday, March 31, 2017 8:55 AMVery handy - Thank you! I made one minor change to provide the correct year for the BeginDate of the fiscal year to date:
select
Convert(varchar(100),'Fiscal Year To Date') as [Period]
,CASE when month(getdate()) < 10
THEN convert(datetime,'10/1/' + cast(year(getdate())-1 as varchar(4))) -- the previous calendar year
ELSE convert(datetime,'10/1/' + cast(year(getdate()) as varchar(4))) -- the current calendar year
END as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
Thanks! I will revise and credit you!
John
March 31, 2017 at 1:45 pm
As specified your "Fiscal Year To Date" logic brings back BeginDate of 2017-10-01 to EndDate of 2017-03-31 when ran on 3/31/2017, Need to subtract one from start (begin) year when the current month is less than the fiscal start month, this brings back the correct result.
select
Convert(varchar(100),'Fiscal Year To Date') as [Period]
,convert(datetime,'10/1/' + cast((case when month(getdate()) < 10 then year(getdate())-1 else year(getdate()) end) as varchar(4))) as BeginDate
,convert(datetime,'10/1/' + cast(year(getdate()) as varchar(4))) as BeginDate
March 31, 2017 at 1:50 pm
rick.foster - Friday, March 31, 2017 1:45 PMVery good for SSRS but it has slight issue, I see this a lot when date functions have a Fiscal Year that do not start on 1/1.As specified your "Fiscal Year To Date" logic brings back BeginDate of 2017-10-01 to EndDate of 2017-03-31 when ran on 3/31/2017, Need to subtract one from start (begin) year when the current month is less than the fiscal start month, this brings back the correct result.
select
Convert(varchar(100),'Fiscal Year To Date') as [Period]
,convert(datetime,'10/1/' + cast((case when month(getdate()) < 10 then year(getdate())-1 else year(getdate()) end) as varchar(4))) as BeginDate
,convert(datetime,'10/1/' + cast(year(getdate()) as varchar(4))) as BeginDate
Thanks; was previously noted and I am in process of revising.
March 31, 2017 at 1:54 pm
Using my suggested changes, here is your view. Please check it out and verify it works.
create view [V_DATE_RANGE] as
select
convert(varchar(100), 'Year To Date') as [Period]
, dateadd(year, datediff(year, 0, getdate()), 0) BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Today') as [Period]
, convert(datetime, convert(date, getdate())) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Yesterday') as [Period]
, convert(datetime, convert(date, dateadd(day, -1, getdate()))) as BeginDate
, convert(datetime, convert(date, dateadd(day, -1, getdate()))) as EndDate
union all
select
convert(varchar(100), 'Fiscal Year To Date') as [Period]
, dateadd(month, -3, dateadd(year, datediff(year, 0, getdate()), 0)) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Month To Date') as [Period]
, dateadd(month, datediff(month, 0, getdate()), 0) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last Month') as [Period]
, dateadd(month, datediff(month, 0, getdate()) - 1, 0) as BeginDate
, dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0)) as EndDate
union all
select
convert(varchar(100), 'Last Month to Date') as [Period]
, dateadd(month, datediff(month, 0, getdate()) - 1, 0) as BeginDate
, dateadd(month, -1, convert(datetime, convert(date, getdate()))) as EndDate
union all
select
convert(varchar(100), 'Last Year') as [Period]
, dateadd(year, datediff(year, 0, getdate()) - 1, 0) as BeginDate
, dateadd(year, datediff(year, 0, getdate()), -1) as EndDate
union all
select
convert(varchar(100), 'Last Year to Date') as [Period]
, dateadd(year, datediff(year, 0, getdate()) - 1, 0) as BeginDate
, dateadd(year, -1, convert(datetime, convert(date, getdate()))) as EndDate
union all
select
convert(varchar(100), 'Last 7 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -7, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 14 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -14, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 21 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -21, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 28 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -28, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 30 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -30, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 60 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -60, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last 90 days') as [Period]
, convert(datetime, convert(date, dateadd(day, -90, getdate()))) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'This Calendar Quarter') as [Period]
, dateadd(quarter, datediff(quarter,0, getdate()),0) as BeginDate
, dateadd(quarter, datediff(quarter,0, getdate()) + 1, -1) as EndDate
union all
select
convert(varchar(100), 'This Calendar Quarter to Date') as [Period]
, dateadd(quarter, datediff(quarter, 0, getdate()), 0) as BeginDate
, convert(datetime, convert(date, getdate())) as EndDate
union all
select
convert(varchar(100), 'Last Calendar Quarter') as [Period]
, dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0) BeginDate
, dateadd(quarter, datediff(quarter, 0, getdate()), -1) EndDate
union all
select
convert(varchar(100), 'Last Calendar Quarter to Date') as [Period]
, dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0) BeginDate
, dateadd(quarter, -1, convert(datetime, convert(date, getdate()))) as EndDate
union all
select
convert(varchar(100), 'Last Year Calendar Quarter') as [Period]
, dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()), 0)) as BeginDate
, dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()) + 1, -1)) as EndDateunion
union all
select
convert(varchar(100), 'Last Year Calendar Quarter to Date') as [Period]
, dateadd(year, -1, dateadd(quarter, datediff(quarter, 0, getdate()), 0)) as BeginDate
, dateadd(year, -1, convert(datetime, convert(date, getdate()))) as EndDate
union all
select
convert(varchar(100), 'Next Year') as [Period]
, dateadd(year, datediff(year, 0, getdate()) + 1, 0) BeginDate
, dateadd(year, datediff(year, 0, getdate()) + 2, -1) EndDate
union all
select
convert(varchar(100), 'Next Year to Date') as [Period]
, dateadd(year, datediff(year, 0, getdate()) + 1, 0) BeginDate
, dateadd(year, 1, cast(cast(getdate() as date) as datetime)) EndDate
GO
March 31, 2017 at 2:02 pm
I will test it this weekend; I'm now writing a revision article and will be sure to feature and credit you and the work.
Thanks
John
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply