My report is querying against the SYSJOBHISTORY table by Date. Since RUNDATE field in SYSJOBHISTORY table is in yyyymmdd format I have to send the date value in yyyymmdd format to query. I created a Date parameter in report with DATETIME datatype. Hence calendar is showing up next to the textbox and when I select a date from the calendar it will pass the date value in yyyy-mm-dd format to query. Which doesn't work to generate the report.

Hi Guest389, try this:- DECLARE @run_date DATE SET @run_date = GETDATE() --SELECT @run_date --SELECT SUBSTRING ( CAST ( @run_date AS VARCHAR(10) ), 6, 2 ) --SELECT LEFT ( @run_date, 4) + SUBSTRING ( CAST ( @run_date AS VARCHAR(10) ), 6, 2 ) + RIGHT ( @run_date, 2) SELECT TOP 10 run_date, * FROM [msdb].[dbo].[sysjobhistory] WHERE run_date = CAST ( LEFT ( @run_date, 4) + SUBSTRING ( CAST ( @run_date AS VARCHAR(10) ), 6, 2 ) + RIGHT ( @run_date, 2) AS INT ) Please let us know your feedback. Thanks KumarKG, MCTS

Sorry.The format of the date is 7/7/2011. I have to convert this to yyyymmdd.

try this:- DECLARE @run_date DATETIME SET @run_date = GETDATE() SELECT CONVERT( VARCHAR(10), @run_date, 101) --SELECT SUBSTRING ( CONVERT ( VARCHAR, @run_date, 110), 4, 2 ), -- CONVERT ( VARCHAR, @run_date, 110) SELECT RIGHT ( CONVERT ( VARCHAR, @run_date, 110), 4) + LEFT ( CONVERT ( VARCHAR, @run_date, 110) , 2) + SUBSTRING ( CONVERT ( VARCHAR, @run_date, 110), 4, 2 ) SELECT TOP 10 run_date, * FROM [msdb].[dbo].[sysjobhistory] WHERE run_date = CAST ( RIGHT ( CONVERT ( VARCHAR, @run_date, 110), 4) + LEFT ( CONVERT ( VARCHAR, @run_date, 110) , 2) + SUBSTRING ( CONVERT ( VARCHAR, @run_date, 110), 4, 2 ) AS INT ) Please let us know your feedback. Thanks KumarKG, MCTS

Thanks for your help. I already fixed it using select convert(VARCHAR,@DATE,112))

Hi Again, Please complete the thread as "Marked" if you issue is fixed otherwise let us know if you have any more issues related to with this thread. Thanks KumarKG, MCTS