CODEWITHSUNDEEP
sql
GabrielVa
GabrielVa

Reputation: 2388

Query Parameters not working

Working on a query here in SSRS and when I use the @Date parameters it doesnt seem to work, I dont get an error but it doesnt return values. When I take the =@Date value out of it, seems to run fine. Any Ideas on how I can correct this? I want to be able to input a name and date.

Thanks

SELECT     TOP (100) PERCENT A.__$start_lsn, A.__$seqval, A.__$operation, A.__$update_mask, A.t_Refcntd, A.t_Refcntu, A.t_cbrn, A.t_ccon, A.t_ccor, A.t_ccrs, A.t_ccty, A.t_ccur, 
                      A.t_cdec, A.t_cfrw, A.t_cfsg, A.t_clan, A.t_comp, A.t_corg, A.t_cotp, A.t_cpay, A.t_cplp, A.t_creg, A.t_ctrj, A.t_cvyn, A.t_cwar, A.t_ddat, A.t_ddtc, A.t_egen, A.t_odat, 
                      A.t_odis, A.t_orno, A.t_prno, A.t_pspr, A.t_pstx, A.t_ragr, A.t_ratf, A.t_ratp, A.t_refa, A.t_refb, A.t_suno, A.t_txta, A.t_txtb, A.tran_begin_time, A.tran_end_time, 
                      B.event_time, B.event_time_local, B.sequence_number, B.action_id, B.succeeded, B.permission_bitmask, B.is_column_permission, B.session_id, 
                      B.server_principal_id, B.database_principal_id, B.target_server_principal_id, B.target_database_principal_id, B.object_id, B.class_type, 
                      B.session_server_principal_name, B.server_principal_name, B.server_principal_sid, B.database_principal_name, B.target_server_principal_name, 
                      B.target_server_principal_sid, B.target_database_principal_name, B.server_instance_name, B.database_name, B.schema_name, B.object_name, B.statement, 
                      B.additional_information, B.file_name, B.audit_file_offset, CONVERT(VARCHAR(10), A.tran_end_time, 110) AS GenericDate
FROM         dbo.ttdpur040101_CT AS A INNER JOIN
                      dbo.ttdpur040101_Audit AS B ON NOT (A.tran_begin_time > B.event_time_local OR
                      A.tran_end_time < B.event_time_local) AND (A.__$operation = 2 AND B.action_id = 'IN' OR
                      (A.__$operation = 3 OR
                      A.__$operation = 4) AND B.action_id = 'UP' OR
                      A.__$operation = 1 AND B.action_id = 'DL') AND B.class_type = 'U'
WHERE     (B.server_principal_name = @Name) and (CONVERT(VARCHAR(10), A.tran_end_time, 110) = @Date)
ORDER BY B.event_time_local

Upvotes: 0

Views: 123

Answers (1)

Kenneth
Kenneth

Reputation: 1364

If you are going to compare them, the formats need to match. Change your WHERE to:

WHERE (B.server_principal_name = @Name) 
and (CONVERT(VARCHAR(10), A.tran_end_time, 110) = (CONVERT(VARCHAR(10),@Date, 110))

Upvotes: 1

Related Questions