TSQL Date format mmmyy

When constructing data for Graphs date formats are required in mmm yy format.  Below function returns date as character variable in mmm yy format.

/****** Object:  UserDefinedFunction [dbo].[MMMYY]    Script Date: 06/13/2013 12:25:52 ******/
SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE FUNCTION [dbo].[Mmmyy] (@Datetime DATETIME,
@nouse    VARCHAR(32))
returns VARCHAR(32)
AS
BEGIN
DECLARE @dt AS VARCHAR(32)

SELECT @dt = Substring(Datename(mm, @Datetime), 1, 3) + ‘ ‘
+ Datename(yy, @Datetime)

RETURN @dt
END

[Credits:]

 

 

 

TSQL Dates

Time and again there comes requests, SSRS report should  run at the first day of the month, or last day of the month or first day or last month.  Listed below are useful TSQL date commands:

— Helper statements for date:
DECLARE @BegDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @pBegDate AS DATETIME
DECLARE @pEndDate AS DATETIME
DECLARE @nBegDate AS DATETIME
DECLARE @nEndDate AS DATETIME
DECLARE @mydate DATETIME
–Current Date
SELECT @mydate = Getdate()
–Previous month end date
SELECT @pEndDate = Dateadd(dd, ( Day(@mydate) ), @mydate)
–Previous month begin date
SELECT @pBegDate = Dateadd(dd, ( Day(@pEndDate)  1 ), @pEndDate)
–Current month begin date
SELECT @BegDate = Dateadd(dd, ( Day(@mydate)  1 ), @mydate)
–Current month end date
SELECT @EndDate = Dateadd(dd, ( Day(Dateadd(mm, 1, @mydate)) ),
Dateadd(mm, 1, @mydate))
–Next month begin date
SELECT @nBegDate = Dateadd(dd, ( Day(Dateadd(mm, 1, @mydate))  1 ),
Dateadd(mm, 1, @mydate))
–Next month end date
SELECT @nEndDate = Dateadd(dd, ( Day(Dateadd(mm, 1, @nBegDate)) ),
Dateadd(mm, 1, @nBegDate)) 

Extract RDL Files from report server database.

At some point of them as a SSRS reports developer the question is bound to pop up, where are the RDL files stored by the report server, more so when the source project solution is not available.  The below script extracts the RDL XML data for all reports from the report server database.

Found this script @ Bret Stateham Blog, which is cool.  Visit Bret’s Blog where this logic is explained in detail – a must read article if you are a SSRS developer.

WITH itemcontentbinaries
AS (SELECT itemid,
name,
[type],
CASE type
WHEN 2 THEN ‘Report’
WHEN 5 THEN ‘Data Source’
WHEN 7 THEN ‘Report Part’
WHEN 8 THEN ‘Shared Dataset’
ELSE ‘Other’
END  AS TypeDescription,
CONVERT(VARBINARY(max), content) AS Content
FROM   reportserver.dbo.catalog
WHERE  type IN ( 2, 5, 7, 8 )),
itemcontentnobom
AS (SELECT itemid,
name,
[type],
typedescription,
CASE
WHEN LEFT(content, 3) = 0xEFBBBF THEN CONVERT(VARBINARY(max),
Substring(content, 4, Len(content)))
ELSE content
END AS Content
FROM   itemcontentbinaries)
SELECT itemid,
name,
[type],
typedescription,
content,
CONVERT(VARCHAR(max), content) AS ContentVarchar,
CONVERT(XML, content)          AS ContentXML
FROM   itemcontentnobom

 

SSRS Report Location

Searching for a report  in multitude of SSRS report folders ?  Not sure where the and in which folder the SSRS report was published, how about extracting the complete list of reports and report location from the report server database.

USE  reportserver
go
SELECT       [name] AS [Report],
[path]  AS [Location]
FROM           dbo.catalog
WHERE        [name] <> 
AND              [type] = 2
ORDER  BY  [name]

 

SSRS Report’s SQL Agent Job Name

Occasionally  there are requests to run a scheduled SSRS report  out of schedule, to be run on demand. In such an event, matching the SSRS report name with SQL Agent Job becomes necessary.  The following TSQL script extracts the SQL Agent Job name for SSRS Reports.

SELECT       cat.name AS Reportrs.scheduleid AS SQLAgentJobName
FROM          reportserver..[catalog] cat           WITH (nolock)
JOIN             reportserver..subscriptions sub  WITH (nolock)
ON                cat.itemid = sub.report_oid
JOIN             reportserver..reportschedule rs  WITH (nolock)
ON                cat.itemid = rs.reportid  AND rs.subscriptionid = sub.subscriptionid
ORDER  BY cat.name