2

SQL Server 2008 introduced new data type datetimeoffset which stores local time and offset between local time and UTC. But when a table containing datetimeoffset column is added to Crystal Reports 2008, the datetimeoffset column is exposed as string in CR Field Explorer rather than DateTime. Is there a way to manipulate datetimeoffset to get the date, time and offset values in CR 2008 other than string conversion? Is there a DateTimeOffset data type in CR 2010?

Sample of datetimeoffset string as displayed in CR 2008:

2009-08-14 16:37:32.0000000 +07:00

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ArtOfCoding
  • 131
  • 1
  • 5

1 Answers1

2

This is the kind of impedance that can easily be countered with a view.

You could create a view representation of that table (or all tables that have the datetime offsets) and use SQL Server's native functions to convert the problematic date time fields into a result set that Crystal Reports can consume without having to resort to string parsing.

If you don't want to bother with a view you can also just write a query that converts from datetimeoffset to a datetime thusly.

Community
  • 1
  • 1
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • Views and stored procedures would work of course, but its not always feasible for us. Often, the reports use direct table linking - CR just needs to handle this new data type properly. I was just wondering if anyone else came across this issue and how did they handle it? Also, if CR 2010 can handle the datatimeoffset properly. – ArtOfCoding Mar 23 '11 at 03:38