I have a daily report. Scenario is i have a date parameter, if i select the date ex: 11/04/2010. i need to grab the data from 11/03/2010 5:30 PM to 11/04/2010 5:29 AM. for daily report iam using the scenario through ssrs in main dataset is DECLARE @DT datetime SET @DT = SUBSTRING(convert(varchar(12),(DATEADD(d,0,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:29:00.000' DECLARE @DT1 VARCHAR(100) SET @DT1 =SUBSTRING(convert(varchar(12),(DATEADD(d,-1,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:30:00.000' select * from from table where DT>=@DT1 AND DT<=@DT another datset is used to pull the DT to use in main dataset select distinct substring(convert(varchar(12),DT,120),0,12) AS Date FROM table. based on above scnario iam getting exact results like if select date parameter: 11/04/2010. reportgenerated data from 11/03/2010 5:30 PM to 11/04/2010 5:29 AM ( in main dataset added time with 24hr format) Now my question is my table generating data from july to august data. in the table i have also month parameter. if i select the date in august month i need to change the above timings instesd of 17 hrs i need display 16hrs timings and need to get the data for above scenario DECLARE @DT datetime SET @DT = SUBSTRING(convert(varchar(12),(DATEADD(d,0,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'16:29:00.000' DECLARE @DT1 VARCHAR(100) SET @DT1 =SUBSTRING(convert(varchar(12),(DATEADD(d,-1,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'16:30:00.000' if i select the date in August month i need to keep the above timings same 17 hrs DECLARE @DT datetime SET @DT = SUBSTRING(convert(varchar(12),(DATEADD(d,0,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:29:00.000' DECLARE @DT1 VARCHAR(100) SET @DT1 =SUBSTRING(convert(varchar(12),(DATEADD(d,-1,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:30:00.000' so how to implement this scenario ( based on month diffrent timings data) help me Thanks in advance

Hi jacks M, From your description, I understand that if the select date is in July, the time should be from 16:30, if the selected date is in August, the time should be from 17:30. If I have misunderstood, please don't hesitate to let me know. If so, we can use the IF...ELSE condition in the query statement, for example: DECLARE @DT datetime DECLARE @DT1 datetime IF Month(convert(datetime,@DTinDate+' 00:00:00.000') ) > =8 BEGIN SET @DT = SUBSTRING(convert(varchar(12),(DATEADD(d,0,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:29:00.000' SET @DT1 =SUBSTRING(convert(varchar(12),(DATEADD(d,-1,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'17:30:00.000' END ELSE BEGIN SET @DT = SUBSTRING(convert(varchar(12),(DATEADD(d,0,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'16:29:00.000' SET @DT1 =SUBSTRING(convert(varchar(12),(DATEADD(d,-1,(convert(datetime,@DTinDate+' 00:00:00.000')))),120),0,12)+ +'16:30:00.000' END SELECT * FROM WHERE DT>=@DT1 AND DT<=@DT If there is anything unclear, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT