1

I'm using crystal report builder and I need to pass to the SQL Server a query with date limitation by Crystal Report parameters.

My SQL table store the datetime as bigint (UTC TICKS).

The user send the parameter for the report selection by calendar selection tool so the received parameter is datetime for the crystal report.

Ho can I perform a query in SQL Command (Crystal Report - command) after changing the parameter value/type? Can I use a formula to change the parameter before using it in the command?

For example: ShiftDateTime({@CreationTime},"UTC,0", "")

Thanks!

user436862
  • 867
  • 2
  • 15
  • 32
  • could you give a few integer values (UTC ticks) and their corresponding UTC datetime. I think i have a solution i will post shortly. – Lee Tickett Mar 24 '12 at 19:30
  • has me a little puzzled actually. the way i understand it `DateDiff('s',CurrentDateTime,DateTime(1,1,1,0,0,0))` should convert the current datetime to utc ticks (multiply by 10,000,000). but then when i try and reverse the number `DateAdd('s',956121718,DateTime(1,1,1,0,0,0))` i get a completely different result :-/ – Lee Tickett Mar 24 '12 at 19:39

1 Answers1

1

I found the answer:

bigint to date: select dateadd(minute ,cast((8123371500/60) as bigint),cast('1753-01-01' as datetime))

date to bigint : select cast(DateDiff(minute,'1753-01-01','2010-06-03 12:05:33') as bigint)*60

thanks anyway.

Shai.

user436862
  • 867
  • 2
  • 15
  • 32