1

Hi Crystal Developers,

I am trying to write a Crystal Report (v2011) that accepts user input for start date and end date. I have set up 2 parameters to do this.

The report connects to the database via a Command object, and I want to reference the parameters in the command object so that data retrieval is quick.

All date data in the database is stored in UTC.

When the user is prompted to enter dates, they are going to input in local time (they are not going to know about UTC...nor should they).

I cannot see a way to convert the user date data into UTC and use it in the command object. Crystal has ShiftDateTime, but this can only be used in formulas, and I don't really want to hard-code a value in the SQL of the command object either.

Any ideas? Thanks

Rod Weir
  • 487
  • 3
  • 5
  • Check this http://stackoverflow.com/questions/1205142/tsql-how-to-convert-local-time-to-utc-sqlserver2008 – David - Jul 05 '13 at 08:32
  • Thanks. I was really looking for a way to do this in Crystal, but I just don't think its possible by using a date parameter. Seems crazy that both Crystal Reports (at the parameter level) and SQL Server don't support daylight savings offsets! – Rod Weir Jul 06 '13 at 03:52
  • 1
    I also posted this question on the Crystal Reports forum, and got a response there. Check it out here http://scn.sap.com/thread/3386687 – Rod Weir Jul 08 '13 at 13:31

1 Answers1

0

Instead of adding the database table within ShiftDateTime you add the user inputted parameter

My user input data from records is "formula": {?DateFrom} to {?DateTo} as opposed to "is Between" {?DateTimeSearch}

Using ShiftDateTime:

DATA.DATE_DATA_UTC = (ShiftDateTime((({?DateFrom}),"","UTC,0")) to (ShiftDateTime((({?DateTo}),"","UTC,0"))
Dharman
  • 30,962
  • 25
  • 85
  • 135
Logan
  • 1
  • Your example does not address the original issue. You cannot use a formula in a database command object. – Rod Weir Apr 20 '20 at 11:22