Fancy a Date?–Dates and Time in SQL and SSRS

Over the last couple of years I have developed Reports and Dashboards for various reasons and for both internal use and clients, and a major part of developing meaningful reports is being able to deal with Date and Time fields.

Most records you will have, and in turn want to report on will have some dates associated

Created on
Modified on
Start/End
Etc.

For example in Microsoft Dynamics CRM, Cases will have a created on, a modified on. The associated Case resolution table in SQL will have a date when the resolution was created.

This is all very useful when it comes to reports, but sometimes you want to do something more meaningful than just show when something was created then when it was resolved. You might want to actually show in hours how long a case is or was open. You might want to see in what week of the month a sales order was taken in order to break down sales order totals by sales person and week – useful for a dashboard.

I thought therefore I would just post up a few SQL queries and expressions that I have found useful, and hopefully you will to.

First of all, what functions can we run against Date/Time fields?

Below is a list of what I would see as the most commonly used Date/Time functions:

Function	Description
DateAdd()	Adds to a date (Days, Weeks etc.)
DateDiff()	Calculates the difference between two dates
DateName()	Returns a string representation of date parts
DatePart()	Returns parts of a date (day,week etc.)
Day()	Returns the day part of a date
GetDate()	Returns the current date and time
Month()	Returns the month part of a date
Year()	Returns the year part of a date

The functions listed above require a date part identifier to be passed to them so below is a list of supported date parts and their abbreviations:

Part	Abbreviation
day	dd or d
dayofyear	dy or y
hour	hh
millisecond	ms
minute	mi or n
month	m or mm
quarter	q or qq
second	ss or s
week	wk or ww
weekday (Only Datepart())	dw
year	yy or yyyy

Those are the building blocks for what we need. There are obviously more options, and there are some good resources around to help find more.

So, how can we use these in producing meaningful reports?

I’ll give a few examples to start.

Internally we use queues in Microsoft Dynamics CRM to hold Cases that are not being worked on for some reason. We wanted to see how long cases had been ‘sat’ in the queue, so querying FilteredQueuItem in SQL we used the following:

datediff (hh, enteredon, getdate())as hoursopen

enteredon was a date/time field so the example above looked at the difference (DateDiff) in Hours (hh) between the date/time the case entered the queue (enteredon) and the current date/time (getdate()) and named the result ‘hoursopen’

This was particularly useful when used in conjunction with an expression when displaying the record on the dashboard, as we used the number that came back for some conditional formatting, so cases open 1-4 hours were green, 5-8 were orange and over 8 red

A second example was used in calculating calls that were made within the last 8 hours. This was actually queried from our telephone system which has a SQL backend.

Every call in the system had a start time called, amazingly, ‘StartTime’

So to pull out calls made within the last 8 hours I used a ‘Where’ clause:

StartTime BETWEEN DATEADD([HOUR], -8, GETDATE()) AND GETDATE())

The where clause is saying, only pull out calls where the StartTime is between now (GetDate()) and 8 hours ago ([Hour], –8, GetDate())

The last example is where I have just pulled out parts of the DateTime to use in charts for grouping etc.

The following uses the same field from cases – createdon – but pulls out the month then the year:

month(FilteredIncident.createdon) as month
year(FilteredIncident.createdon) as year

This was useful to show a trend of cases created within certain years or months.

I used the following expression in the chart label to render the resulting number (1-12 for 12 months of the year) to show the corresponding month name:

=Choose (Fields!month.Value , "Jan", "Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

The point of this blog was not to give exhaustive examples, but to give a flavour of what could be accomplished using the date/time fields. Data is only useful when something meaningful is pulled out. I could have 100 cases open, all recorded in CRM,  but when I can see that 30% of them are over 8 hours old, I can do something.

Just FYI I tend to use BIDS or SQL Report Builder to put my reports together as opposed to the internal reporting feature of CRM as I find them a whole lot more flexible.