StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I assume you use SQL Server 2008 or later as you have a <code>CAST(... as date)</code> in your code</p> <p>There are several issues with your stored procedure:</p> <ol> <li>A weird way to strip time (just cast to date)</li> <li>A BETWEEN statement that compares <strong>strings</strong> instead of dates, because of the <code>+''</code> statements.</li> <li>Using <code>datetime</code> parameters when you actually need just <code>date</code></li> </ol> <p>The simplest way would be to rewrite the stored procedure like this</p> <pre><code>ALTER procedure [dbo].[Driverperformance] @Ecode nvarchar(50), @startdate date, @enddate date as begin SELECT e.Ecode,CAST(q.dtime AS DATE) as Date, e.Ename, count(q.Ecode) AS CntEcode , count(DelEcode) AS CntDelEcode FROM EmployeeMaster_tbl e inner JOIN Transaction_tbl q ON e.Ecode = q.Ecode where q.Ecode=@Ecode and @startdate <= dtime and dtime < @enddate group by e.Ecode, e.Ename, CAST(q.dtime AS date) ORDER BY CAST(q.dtime AS date)--e.Ecode DESC end </code></pre> <p>You shouldn't have problems with code that calls this stored procedure and passes <code>datetime</code> values because the time portions will be truncated. You will encounter problems only if you pass string literals instead of dates, in formats that SQL Server can't identify from your settings</p> <p>If you have to use <code>datetime</code> parameters in some other part of your stored procedure, you can do a simple cast to date:</p> <pre><code>ALTER procedure [dbo].[Driverperformance] @Ecode nvarchar(50), @startdate datetime, @enddate datetime as begin declare @date1 date= cast(@startdate as date); declare @date2 date= cast(@enddate as date); SELECT e.Ecode,CAST(q.dtime AS DATE) as Date, e.Ename, count(q.Ecode) AS CntEcode , count(DelEcode) AS CntDelEcode FROM EmployeeMaster_tbl e inner JOIN Transaction_tbl q ON e.Ecode = q.Ecode where q.Ecode=@Ecode and @date1 <= dtime and dtime < @date2 group by e.Ecode, e.Ename, CAST(q.dtime AS date) ORDER BY CAST(q.dtime AS date)--e.Ecode DESC end </code></pre> <p>You can also get rid of the <code>ORDER BY</code> statement by reordering your <code>GROUP BY</code>. Grouping orders the results, so you can use <code>GROUP BY CAST(q.dtime AS date) , e.Ecode, e.Ename</code> to get your results order by date, Ecode and Ename</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload