Stored procedure returns rows but cannot see them in Visual Studio

Transact-SQL
1

Hi Guys,

I have a simple stored procedure that returns rows based on the 2 datatime parameters:
SELECT * FROM MyDB
WHERE CONVERT(varchar(24), @LastRun, 120) < CreationDate
AND CreationDate < CONVERT(varchar(24), @EndDate, 120)

The 2 parameters are datetime parameters.
It returns some records, works fine.

I call this SP from Visual Studio:
SqlCmd.CommandText = "SP_Name";
SqlCmd.Parameters.Add("@LastRun", SqlDbType.DateTime).Value = lastRun;
SqlCmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
return ExecuteStoredProc(SqlCmd);

My problem is that it returns no rows.
The connection string uses an account/password pair of a SQL DB user.

Any ideas? Thanks in advance!

2

Is the datatype of that column DATE or DATETIME?

If not it would be better if it was!

If so then don't convert your @LastRun and @EndDate to varchar before comparing against CreationDate (although, in fairness, you are testing a yyyy-mm-dd hh:mm:ss text format which should sort strictly in chronological order, my only real gripe is that the performance will be much slower than a strict DATE / DATETIME comparison).

3

It is DATETIME

I tried without converts:
SELECT * FROM MyDB
WHERE @LastRun < CreationDate
AND CreationDate < @EndDate

but it returned no rows :frowning:

4

So I was able to solve the problem this way:
Modified the 2 datatime parameters of the SP to NVARCHAR(20). So now the SP's select looks like this:

SELECT * FROM MyDB
WHERE CONVERT(DATETIME, @LastRun, 20) < CreationDate
AND CreationDate < CONVERT(DATETIME, @EndDate, 20)

I take care about these on Visual Studio side in C#, I provide this kind of parameter values: 2016-05-04 12:11:20
This way the SP runs well and can see the result records in Visual Studio as well.

If somebody run into the same problem and found an another solution please share it.

Thanks!

5

If you can transfer your Value from C# in a native Date / Datetime object that should take care of that problem.

Otherwise if your SProc is defined as

CREATE PROCEDURE MySproc
    @LastRun datetime,
    @EndDate datetime
AS

then SQL will make an implicit conversion of the parameters into DATETIME. if you pass the @Date parameters are passed as "text" then if you use the format 'yyyymmdd' (NO punctuation/spaces) then SQL will convert them unambiguously. Other unambiguous formats are 'yyyymmdd hh:mm:ss' and the ISO format 'yyyy-mm-ddThh:mm;ss'

If you use any other string format for the dates then SQL will make an attempt to parse the date according to various Locale settings - unfortunately those include things like the Language of the currently connected user, so they can be very fragile and lead to errors that are difficult to solve.

That said, I would have expected a string in the format "yyyy-mm-dd hh:mm:ss" to convert unambiguously to a DATE / DATETIME, but maybe there is something in your Locale that is mucking it up.

Using an explicit CONVERT of your NVARCHAR to DATE TIME, as you have done, will solve the problem.

6

Thanks!