SSRS Date format expressions

Information gleamed from http://www.sqlservercentral.com/Forums/Topic290724-150-3.aspx

The Format function is locale aware and can be used as follows [not the case senstivity]
Set Language=User!language in the Report properties and use the Format function with the following codes:

Standard Format Specifiers for Dates and Times:
The table below shows the standard date and time formatters.

d Short Date
D Long date
f long date & short time
F long date and long time
g short date and short time
G short date and long time
M or m month and day
Y or y year and month
t short time
T long time
s displays in ISO 8601 format using local time
u displays in ISO 8601 format using universal time
U date and time in unversal time
R or r displays in RFC 1123 format

Custom formatting sequences:
There are also specific character sequences that can be used to achieve custom formatting of dates and times.

Format Description

d day of month (1 or 2 digits as required)
dd day of month (always 2 digits, with a leading 0 if needed)
ddd day of week (3 letter abbreviation)
dddd day of week (full name)
M month number (1 or 2 digits as required)
MM month number (always 2 digits, with a leading 0 if needed)
MMM month name (3 letter abbreviation)
MMMM month name (full name)
y year ( last 1 or 2 digits, no leading 0)
yy year (last 2 digits)
yyyy year (4 digits)
H hour in 24-hour format (1 or 2 digits as required)
HH hour in 24-hour format (always 2 digits, with a leading 0 if needed)
h hour in 12-hour format (1 or 2 digits as required)
hh hour in 12 hour format
m minutes (1 or 2 digits as required)
mm minutes (always 2 digits, with a leading 0 if needed)
s seconds (1 or 2 digits as required)
ss seconds
t first character in the am/pm designator
tt am/pm designator
z time zone offset, hour only (1 or 2 digits as required)
zz time zone offset, hour only (always 2 digits, with a leading 0 if needed)
zzz time zone offset, hour and minute
/ default date separator
: default time separator
\ escape characters

Webinar videos on SQL SERVER

http://www.pragmaticworks.com/Resources/webinars/Default.aspx

I attended the webinar for “Identifying and Fixing Performance Problems using Execution Plans” as it was excellent !

The link above points to the page that holds many other related webinars.  A fantastic source of information.

[SQL] ReportServer Catalog queries

–the following tsql maps out the folder structures, including parent-child relationships
–Use ReportServer;
SELECT
SUBSTRING(c.path,1,CHARINDEX(‘/’,c.path,2)) [Section],
c.Path
, c.Name [ChildName]
, c.Description
, c.Type
, CASE c.TYPE WHEN 1 THEN ‘Folder’
    WHEN 2 THEN ‘Report’
    WHEN 3 THEN ‘Image’
    WHEN 4 THEN ‘Linked Report’
    WHEN 5 THEN ‘Data Source’
    WHEN 6 THEN ‘Model’
    ELSE ‘Type:’+CONVERT(varchar(2),c.Type)
    END [TypeDesc]
, Parent.Name [ParentName]
, c.CreationDate
, c.ModifiedDate
, ISNULL(c.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog c
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = c.ParentID
ORDER BY c.Path, Parent.Name
–tsql to list the assigned users/roles to each FOLDER
SELECT
c.Path
, u.UserName
, r.RoleName
FROM dbo.Catalog c
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = c.ParentID
INNER JOIN dbo.PolicyUserRole pur ON pur.PolicyID = c.PolicyID
INNER JOIN dbo.Users u ON u.UserID = pur.UserID
INNER JOIN dbo.Roles r ON r.RoleID = pur.RoleID
WHERE c.Type IN (
1 — Folder
)
ORDER BY c.Path, u.UserName

[SSRS] Finding SSRS subscription job details

Below, we will generate the T-SQL that is used inside the SQL Agent job directly from the tables and then just run the SQL.
It’s a pure SQL based solution.

After you run this query, the [ReportCommand] column that has the EXEC command
for the report you want to run and paste the EXEC command into a query window to execute the code to re-run the report.

NOTE:
It’s possible for a ReportSchedule record to exist, but not have a corresponding Subscription !

SELECT 'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', ' '@EventData='''+CAST(sub.SubscriptionID AS VARCHAR(40))+'''' [ReportCommand] , sysjob.name AS JobName , sub.SubscriptionID , cat.name , cat.path , sub.description , sub.laststatus , sub.eventtype , sub.LastRunTime , sysjob.date_created , sysjob.date_modified from ReportServer.dbo.Catalog cat JOIN ReportServer.dbo.Subscriptions sub ON sub.report_oid = cat.ItemID JOIN ReportServer.dbo.ReportSchedule sched ON sched.SubscriptionID = sub.SubscriptionID JOIN msdb.dbo.sysjobs sysjob ON CAST(sched.ScheduleID AS VARCHAR(40)) = sysjob.name --WHERE cat.[path] LIKE '%job%' WHERE sub.LastRunTime >= '19-oct-2010' ORDER BY sub.LastRunTime