Working with Dates in Dashboard Designer Grids

David_Darling

Hi -- How are people dealing with the fact we can't convert dates in the grids to local time?

Thanks!

Geoff_Ross

Hi David,

I would just manipulate the string in SQL.

eg 

SELECT WorkItemId, CONVERT(varchar,Created,103) + ' ' + CONVERT(varchar,DATEADD(hh,-6,Created),108) [Created_Date] From WorkItem

NB: You might need to change the '103' to '101' if your servers are set to use a US time format. Mine are in UK format and 103 converts to US but I think if you are already in US, you need 101. Either way, try both and see,

Also, proper local time is tricky and your off set to UTC will change through the year. I've put '-6' in here but you will need to adjust for your time zone but I'm not quite sure what to do around daylight saving. Maybe its best to lose the time and just have date anyway, which makes grouping easier.

Good luck with it

Geoff

Tom_Hendricks

Geoff_Ross said:

Hi David,

I would just manipulate the string in SQL.

....

For some reason I thought T-SQL functions did not work in the dashboards.  In this case, I would ignore my JS code and use Geoff's SQL. 

Thanks for clearing that up!

Tom_Hendricks

I haven't run across this yet (waiting to hit another milestone before using the dashboards), but if you are able to reference the grid cells with JavaScript, there is a function for converting them built into the portal:

app.lib.getLocalDateFromUTCDate(yourDate)

Running this from within $(document).ready(function() { .... }); should work, in theory, if you can iterate across the grid and plug in the yourDate (my name for it) param for date cells.

Having said all of this, maybe someone else has a simpler way of accomplishing the same?

Geoff_Ross

Hi David,

I would just manipulate the string in SQL.

eg 

SELECT WorkItemId, CONVERT(varchar,Created,103) + ' ' + CONVERT(varchar,DATEADD(hh,-6,Created),108) [Created_Date] From WorkItem

NB: You might need to change the '103' to '101' if your servers are set to use a US time format. Mine are in UK format and 103 converts to US but I think if you are already in US, you need 101. Either way, try both and see,

Also, proper local time is tricky and your off set to UTC will change through the year. I've put '-6' in here but you will need to adjust for your time zone but I'm not quite sure what to do around daylight saving. Maybe its best to lose the time and just have date anyway, which makes grouping easier.

Good luck with it

Geoff

Geoff_Ross

Here's the final result BTW.

Tom_Hendricks

Geoff_Ross said:

Hi David,

I would just manipulate the string in SQL.

....

For some reason I thought T-SQL functions did not work in the dashboards.  In this case, I would ignore my JS code and use Geoff's SQL. 

Thanks for clearing that up!

Chris_Stelzer

This date formatting doesn't work properly for date sorting. Using '111' appears to fix the sorting of dates in the grid views.

SELECT WorkItemId, CONVERT(varchar,Created,111) + ' ' + CONVERT(varchar,DATEADD(hh,-6,Created),108) [Created_Date] From WorkItem

Brian_Wiest

Cireson released a fix with portal 7.3 that it adjusts the dates from UTC to local. 
However there are some bugs that are scheduled to be fixed in 7.4 for other date columns. 
I removed all the converts and it works on Create date column
Of note when using the convert it changes to string so depending on your convert code it could sort in an unexpected manner.