<div>
Hello MrAgile, 
 
Convert(varchar(10),@Start,113) + ' 00:00:00:000' 
Convert(varchar(10),@End,113) + ' 23:59:59:999' 
 
Is possibly the easiest way. 
 
Regards, 
 
TimCottee
</div>

<div>
Try the code below - 
 
 

<pre><code class="code-10 nolinks" id="code-261111">declare @start datetime, @end datetime
 
select	 @start = getdate() - 5 -- This is the sample input start date
	,@end	= getdate() + 1 -- This is the sample input end date
 
select 	 @start = dateadd(d, datediff(day, 0, @start), 0)
	,@end = dateadd(s, -1, dateadd(day, 1, dateadd(d, datediff(day, 0, @end), 0)))
 
select	 startdate	= convert(varchar(11), @start, 120) + right(convert(varchar(30), @start, 100), 7)
 	,enddate	= convert(varchar(11), @end, 120) + right(convert(varchar(30), @end, 100), 7)
</code></pre>
</div>

<div>
Does it actually have to be 23:59:59 on that day or will 00:00:00 the following day be OK? 
 
As a simple way when I am writing report procedures that take a start and enddate as parameters I just use: 

<pre><code class="code-1 nolinks" id="code-261713">CREATE PROCEDURE MyProc (@startDate DATETIME, *endDate DATETIME)
AS
SELECT columns
FROM tables
WHERE dateCriteria BETWEEN @startDate AND @DATEADD(dd,1,@endDate)
</code></pre>
</div>

<div>
OK, typos galore but you get the point. With a milisecond in it then this is more efficient and easier to maintain.
</div>

<div>
Hi sampipes, the trouble with that is the between clause is inclusive, and whilst most datetime should have a time component, if they don't then there will be undersirable results. So what you can do is say : 
WHERE dateCriteria &gt;= @startDate AND dateCriteria &lt;&nbsp;@DATEADD(dd,1,@endDate) &nbsp; &nbsp;-- basically expanding what the between will equate to anyway or.... 
WHERE dateCriteria BETWEEN @startDate AND dateadd(ms,-3,@DATEADD(dd,1,@endDate)) 
 
And the latter one could be done before the query e.g. set @enddate = dateadd(ms,-3,@DATEADD(dd,1,@endDate)) &nbsp; -- so long as it does not have a time component. 
And that last comment is pretty important, those variables should be resolved before use in the query to give the query optimiser every chance to work optimally.. 

</div>

<div>
Hi There, 
 
Thanks to all for the responses. I will try each one of them and then let you know how I go with it. 
 
Sean
</div>

<div>
Hi Mark, 
 
The solution that you provided worked perfectly for me. I appreciate the help. 
 
Sean
</div>

<div>
<div class="content wysiwyg-content">
Hi There, 
 
I was wanting to add hours,mins,secs to some data parameters that I have in my stored proc. Can someone recommend the best way to do this? The times wont change as I need the start to be at midnight and the finish to be at 11.59. 
 
Thanks in advance, Sean 
 
 

<pre><code class="code-10 nolinks" id="code-261043">---param
 
convert(varchar(20),@Start,113)
 
convert(varchar(20),@End,113)
 
---actual output
@Start='2009-03-21 00:00:00:000'
@End='2009-03-27 00:00:00:000'
--desired output
 
@Start='2009-03-21 12:00:00 AM'
@End='2009-03-27 11:59:59 PM'
</code></pre>
</div>
</div>

Hi There,

I was wanting to add hours,mins,secs to some data parameters that I have in my stored proc. Can someone recommend the best way to do this? The times wont change as I need the start to be at midnight and the finish to be at 11.59.

Thanks in advance, Sean



Adding hours, mins, secs to a dateparameter in stored procedure

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.