SQL Server
SQL : Useful DateTime Queries
SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’
—-First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)’First Day Of Current Quarter’
—-Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0)) ‘Last Day Of Current Quarter’
—-First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0) ‘First Day Of Prior Quarter’
—-Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0)) ‘Last Day Of Prior Quarter’
What are SSRS, SSIS and SSAS
Let me give a brief introduction of SSRS, SSIS and SSAS as
you can get more details on these topic if you google it.
Microsoft SQL Server Reporting
Services (SSRS) delivers enterprise, Web-enabled reporting functionality so you
can create reports that draw content from a variety of data sources, publish
reports in various formats, and centrally manage security and subscriptions.
Microsoft
SQL Server Reporting Services enables organizations to transform valuable
enterprise data into shared information for insightful, timely decisions at a lower
total cost of ownership.
SQL Server Reporting Services is a comprehensive, server-based solution that
enables the creation, management, and delivery of both traditional,
paper-oriented reports and interactive, Web-based reports. An integrated part
of the Microsoft Business Intelligence framework, Reporting Services combines
the data management capabilities of SQL Server and Microsoft Windows Server
with familiar and powerful Microsoft Office System applications to deliver
real-time information to support daily operations and drive decisions.
Services
It is a Data Warehousing Tool; SSIS
is one of the key features introduced in SQL Server and is the new DTS (Data
Transaction Services) platform. Microsoft
Integration Services is a platform for building enterprise-level data
integration and data transformations solutions. You use Integration Services to
solve complex business problems by copying or downloading files, sending e-mail
messages in response to events, updating data warehouses, cleaning and mining
data, and managing SQL Server objects and data. The packages can work alone or
in concert with other packages to address complex business needs. Integration
Services can extract and transform data from a wide variety of sources such as
XML data files, flat files, and relational data sources, and then load the data
into one or more destinations.
rich set of built-in tasks and transformations; tools for constructing packages;
and the Integration Services service for running and managing packages. You can
use the graphical Integration Services tools to create solutions without
writing a single line of code; or you can program the extensive Integration
Services object model to create packages programmatically and code custom tasks
and other package objects.
Microsoft SQL Server 2005 Analysis
Services (SSAS) delivers online analytical processing (OLAP) and data mining
functionality for business intelligence applications. Analysis Services supports
OLAP by letting you design, create, and manage multidimensional structures that
contain data aggregated from other data sources, such as relational databases.
For data mining applications, Analysis Services lets you design, create, and
visualize data mining models that are constructed from other data sources by
using a wide variety of industry-standard data mining algorithms.
Dynamics Tips 