Copy and paste the statement below into a new query in SSMS and run it to experiment with different variables. Refer to the comments for guidance on what variables are allowed. Once again enddate is derived to take account of the current date, such as if year is the current year, etc.
1 DECLARE @DateView varchar (20)
2 , @theDate varchar (20)
3 , @startDate datetime
4 , @endDate datetime
5
6 SELECT @DateView = 'Yearly'
7 --enter the date view required: Select from 'Daily', 'Weekly', 'Monthly', 'Yearly' and 'Last 12 Months' or 'All'
8 , @thedate = '2007'
9 --For Daily enter the date in the format 'YYYYMMDD' - e.g. '20060901' for September 1st 2006
10 --For Weekly enter the week ending date in the format 'YYYYMMDD'. Note that the week ends on a Sunday - e.g. '20060910' for September 10th 2006
11 --For Monthly enter date in the format 'YYYYMM' - e.g. '200609' for September 2006
12 --For Yearly enter the date in the format 'YYYY' - e.g. '2006'
13 --For Last 12 Months enter the date is not required as it is calculated up to the current date
14 --Do not update startdate and enddate below:
15 , @startDate = case
16 when @DateView = 'Daily'
17 then cast(@thedate as datetime)
18 when @DateView = 'Weekly'
19 then dateadd(dd,-6,cast(@thedate as datetime))
20 when @DateView = 'Monthly'
21 then cast(@thedate+'01' as datetime)
22 when @DateView = 'Yearly'
23 then cast(@thedate as datetime)
24 when @DateView = 'Last 12 Months'
25 then dateadd(mm,-11,cast(cast(datepart(yyyy,getdate()) as varchar(4))+right('0'+cast(datepart(mm,getdate()) as varchar(2)),2) + '01' as datetime))
26 else cast('2005-01-01' as datetime)
27 end
28 , @endDate = case
29 when @DateView = 'Daily'
30 then dateadd(dd, 1, @startDate)
31 when @DateView = 'Weekly'
32 then (
33 case when cast(@thedate as datetime)+1 > getdate()
34 then cast(CONVERT(char(10), getdate(), 110) as datetime)
35 else cast(@thedate as datetime)+1
36 end
37 )
38 when @DateView = 'Monthly'
39 then (
40 case when dateadd(mm, 1, @startDate) > getdate()
41 then cast(CONVERT(char(10), getdate(), 110) as datetime)
42 else dateadd(mm, 1, @startDate)
43 end
44 )
45 when @DateView = 'Yearly'
46 then (
47 case when dateadd(mm,12,@StartDate) > getdate()
48 then cast(CONVERT(char(10), getdate(), 110) as datetime)
49 else dateadd(mm,12,@StartDate)
50 end
51 )
52 when @DateView = 'Last 12 Months'
53 then cast(CONVERT(char(10), getdate(), 110) as datetime)
54 else cast(CONVERT(char(10), getdate(), 110) as datetime)
55 end
56
57 print @thedate
58 print @startdate
59 print @enddate
One of the big advantages of this method is that only one query is required to easily return data for any of the common date ranges, because of the use of the date view variable. To use this, simply paste it above the query desired (without the print commands), and use @startdate and @enddate in the where clauses, for example: