Select records in a table depending on the time range for the dates
I am using a dynamic SQL query which displays transactions between certain dates. But now I am asked to implement within a certain time range too. But if i add a start time and end time and concatenate the date and time strings from the web app and pass it as the @fromdate and @todate parameters in the code below, it gives results in such a way that the starting from the start date till end time on the end date.
But i want to modify it in such a way that if i enter my dates as 1/12/2008 - 1/13/2008
and times as 12:00 am - 2:00 am...then it should display results only for those two hours for both the days, rather than displaying from 12:00 am on 12th to 2:00 am on 13th.
How can i modify the query or the c# code. Please help me.
td_datetimestamp between coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)