SQLTeam.com | Weblogs | Forums

Crystal Report SQL Query command paramter with formula editor value


Hello Sql Developer's,

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", "")



Not clear of your question, can you post screen shots?


you would have to create maybe your own function? for example one that converts a date to ticks bigint

declare @CreationTime datetime2 = getdate()
declare @CreationTimetick bigint = [dbo].[ToTicks] (@CreationTime)

create table #shifts(shiftid int, shiftFrom bigint, shiftTo bigint)

;with cte
	select 1 as shiftid, [dbo].[ToTicks] ('2018-01-12 08:37:21.593') shiftFrom, [dbo].[ToTicks] ('2018-01-12 11:37:21.593') shiftTo union
	select 2, [dbo].[ToTicks] ('2018-01-12 09:37:21.593'), [dbo].[ToTicks] ('2018-01-12 11:37:21.593') union
	select 3, [dbo].[ToTicks] ('2018-01-11 10:37:21.593'), [dbo].[ToTicks] ('2018-01-12 12:37:21.593') union
	select 4, [dbo].[ToTicks] ('2018-01-12 11:37:21.593'), [dbo].[ToTicks] ('2018-01-12 13:37:21.593') union
	select 5 , [dbo].[ToTicks] ('2018-01-10 12:37:21.593'), [dbo].[ToTicks] ('2018-01-12 14:37:21.593') 
insert into #shifts
select * from cte

select * From #shifts 
where shiftFrom >= @CreationTimetick and ShiftTo <= @CreationTimetick
drop table #shifts