CREATE FUNCTION get_week_start (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
CREATE FUNCTION get_week_end (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
• "Get Month Start" and "Get Month End" return the start and end of the current month.
CREATE FUNCTION get_month_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
CREATE FUNCTION get_month_end (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
• "Get Yesterday Start" and "Get Yesterday End" return the start and end of the day prior to the parameter.
CREATE FUNCTION get_yesterday_start (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
CREATE FUNCTION get_yesterday_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
• "Get Today Start" and "Get Today End" represent the start and end of the date passed.
CREATE FUNCTION get_today_start (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
CREATE FUNCTION get_today_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
• "Get Weekday Start" and "Get Weekday End" return the start and end of the weekday specified within the week passed as date. For example, to get the start and end of Tuesday of the current week, pass the parameters 2 and getdate().
CREATE FUNCTION get_weekday_start (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
CREATE FUNCTION get_weekday_end (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
• In a similar fashion, the following functions generate dates as indicated in the function name.
CREATE FUNCTION get_year_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
CREATE FUNCTION get_tomorrow_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
CREATE FUNCTION get_today_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
Microsoft.com Operations Virtualizes MSDN and TechNet on Hyper-V
Microsoft migrated MSDN to Hyper-V on March 31, 2008, and then followed up with TechNet on April 18, 2008. This article provides further details about testing methods and the results from Hyper-V Beta to RC0 that generated the confidence to fully roll out MSDN and TechNet on Hyper-V in production. »
________________________________________
Virtualization Case Study: Copa Airlines
Always looking to improve server availability and efficiency. Copa Airlines joined a Microsoft Rapid Deployment Program to test Hyper-V to provide a dynamic and reliable virtualization environment and System Center Virtual Machine Manager for the physical to virtual conversions of the chosen servers. The RDP program proved that a Microsoft virtualization solution could be a cost-effective way for Copa to increase business application availability, reduce data center costs, and optimize data center management. »
________________________________________
Virtualization from the Data Center to the Desktop
Integrated virtualization solutions from Microsoft can help you meet evolving demands more effectively as you transform your IT infrastructure from a cost center to a strategic business asset. »
________________________________________
Interoperability Advantages with Windows Server 2008
Learn about the advantages of using Windows Server 2008 with Windows Vista, and find information on how Windows Server 2008 interoperates with other applications and platforms. »
________________________________________
Windows Server 2008 News & Reviews
Keep up to date on the latest Windows Server 2008 news from Microsoft and independent sources worldwide. Here you'll find a compilation of technical reviews, news reports, and press releases about Windows Server 2008 and related products, services, and technologies. »
•An Android in Every Pocket? That's Google's Plan October 24, 2008
•PDC is Not Just the Windows 7 Show October 24, 2008
•How Much Is a Linux Distro Worth? October 22, 2008
•G1 Floats into Market with Fewer Apps October 22, 2008
Webcast: Palm Developer Network Technical Series. The Webcast Series will cover technical topics designed to help you build, debug and market your applications.
Dynamic Default Date Parameters in SQL Server 2000 Reporting Services
By Paul Whitaker
Go to page: Prev 1 2
Selecting Common Dates
These functions are great general purpose date calculators. To better integrate with Reporting Services, I've created a function and stored procedure to populate an RS dataset.
The following is a table-valued function that returns all of the calculated dates. It allows you to SELECT from the list to get only the parameters you need.
CREATE FUNCTION udfCommonDates (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
tomorrow_noon datetime,
today_noon datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,RETURN
END
The following stored procedure gets the pertinent dates based on the current date.
CREATE PROCEDURE uspCommonDates AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Integrating with Reporting Services
Pulling these dates into Reporting Services via the Report Designer is a breeze. In the Data tab, simply add the uspCommonDates stored procedure as a new Dataset.
Now that you have a Dataset of commonly used dates, they are now available to you to set as Default values for the parameters. Simply select the new dataset and choose whichever "Value field" matches your desired date.
Conclusion
Business intelligence requirements are often bound to relative dates such as the previous day, previous week, or previous month. Generating a list of commonly used dates and setting the appropriate dates as default report parameters can facilitate the easy generation of time-based reports.
No comments:
Post a Comment