Compare C# Date with SQL Date within a Query

Question

I'm trying to compare a C# DateTime with a SQL-server DateTime in a Stored Procedure but it keeps giving me convert-errors.

At first someone else made the Oracle function for this:

'Select blabla from bla WHERE (TO_DATE (''' + cast(@dateEnd as varchar(50)) + ''',''yyyy/mm/dd'') >= SPOT_ENDDAT) 

And I'm trying to change this to SQL but in SQL you don't have the TO_DATE function.

Any ideas? Or should I make the changes at the level of my .net program itself? If yes, what should I do?

EDIT:

Calling my function Stored Procedure with this parameter :

DateTime EndDate = DateTime.Today;

ParamList.Add(new <class>.Parameter("EndDate", ParameterDirection.Input, EndDate, DbType.Date));

Stored Procedure:

ALTER PROCEDURE dbo.uspGetValues
         @EndDate = null;
         AS
         BEGIN
         SET NOCOUNT ON;

         DECLARE @SQL as NVARCHAR(4000)
         Set @SQL = 'SELECT * FROM T_SPOTSHOP_DATA WHERE SPOT_ENDDATE IS NOT NULL'
         if(@EndDate is not null)
         Set @SQL = @SQL + 'AND (' + @EndDate +' <= SPOT_ENDDATE' 

         EXEC(@SQL)

Edit Solution:

For those who have the same problem I fixed it the other-way around. In C# I would used :

DateTime EndDate = DateTime.Today.toString(yyyy-MM-dd);

ParamList.Add(new <class>.Parameter("EndDate", ParameterDirection.Input, EndDate, DbType.Date));

and I catch it up in my stored procedure as:

EndDate Varchar(50)

SET @SQL = @SQL + 'WHERE CONVERT(DATETIME, '''+ @EndDate +''', 121) >= SPOT_ENDDATE

It's a quite ugly way to do it but it works. Hopes it helps you guys!

Answer 1

try this:

Select 
    blabla 
from 
bla 
 WHERE CAST(@dateEnd AS DATETIME) >= CAST(SPOT_ENDDAT AS DATETIME)

Answer 2

You can perform the conversion in T-SQL using CONVERT, but I wouldn't.

I would strongly recommend avoiding string conversions as far as possible. Just use parameterized SQL, and specify the parameter as a DateTime:

// Assuming dateEnd is a DateTime variable
string sql = "SELECT blabla FROM bla WHERE @dateEnd >= SPOT_ENDDAT";
using (var command = new SqlCommand(conn, sql))
{
    command.Parameters.Add("@dateEnd", SqlDbType.DateTime).Value = dateEnd;
    // Execute the command here
}

I'd do the equivalent for Oracle as well - unless your task really inherently involves converting between text and the "native" data type, don't do it.

Or use a LINQ provider of course, at which point you'll have a more readable query to start with :)

Answer 3

Just pass the .Net DateTime value as a parameter to the Sql Command. The database driver will handle the conversion to an Sql Server date time automatically.