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!

remember to make your C# DateTime string the SAME format as the one you are comparing with in SQL Server. — Ahmed ilyas, Dec 09 '13 at 09:38

score 0 · Answer 1 · answered Dec 09 '13 at 09:37

0

try this:

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

answered Dec 09 '13 at 09:37

Preet Sangha

64,563
18
145
216

When I'm trying this I get this error: "System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string" – Akorna Dec 09 '13 at 10:07

score 0 · Answer 2 · answered Dec 09 '13 at 09:38

0

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 :)

answered Dec 09 '13 at 09:38

Jon Skeet

1,421,763
867
9,128
9,194

I'd love to do this but I'm working with a Dataprovider so I'm working with parameterlists kindoff like this: `paramList.Add(new class.Parameter("EndDate", Parameter.direction.Input, search.EndDate, DbType.Date)); ` And I can't put SqlDbType there sadly, or I just don't know how, I'm very bad at databaseAccesses – Akorna Dec 09 '13 at 10:09
@Akorna: Well if you're using `DbType.Date` that should probably be enough - you're at least not converting it to a string. – Jon Skeet Dec 09 '13 at 10:45
Well it still gives the `System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string` ...so for some reason he keeps converting it somewhere into a string? – Akorna Dec 09 '13 at 10:56
@Akorna: Please give *full details* - including the type of the variable you're using as the value. Currently we don't have enough context. – Jon Skeet Dec 09 '13 at 10:58
I edited the question with more detailed information of what I'm trying at this point – Akorna Dec 09 '13 at 11:11
@Akorna: Ah - it looks like your *stored procedure* is converting the value to a string. Why are you doing that? (Additionally, the stored proc seems to be confused about what the parameter is called... my guess is that that isn't the real stored proc. It would be really helpful if you could get this to the stage where you've got a short but *complete* example where you can give us the schema, stored proc and C# code, rather than just pseudocode – Jon Skeet Dec 09 '13 at 11:12
I can't add more then what I just edited at this moment. The one thing I could think about to do would be to format the string beforehand but even `DateTime try = DateTime(ParseExacte(DateEnd.toString("yyyy-MM-dd"), "yyyy-MM-dd", CultureInfo.InvariantCulture);` can't change the format of the date... this would be a change in c# ofcourse – Akorna Dec 09 '13 at 12:54
@Akorna: No, you absolutely should *not* be formatting and reparsing the value. But fundamentally your stored proc is still creating SQL on the fly, which is nasty. Why are you doing that? – Jon Skeet Dec 09 '13 at 12:55
Because the procedure is much larger with much more possible variables that can be checked and they all work fine except the datepart. It's a task that comes from my internship and I don't really have a choice to do it another way. Yes it's quite ugly programming, but it does what it should like they'd want it. And the only thing that doesn't works is the date comparason – Akorna Dec 09 '13 at 12:58
Thank you for not giving up on me – Akorna Dec 09 '13 at 12:58
@Akorna: I would suggest that you dump a diagnostic log with the generated SQL. At this point it's really not got a lot to do with .NET though - you've got a DateTime value in your stored proc, so it's all about what you do with it in your stored proc. – Jon Skeet Dec 09 '13 at 12:59
I've never made a diagnostic log, could you clarify what you expect to get? – Akorna Dec 09 '13 at 13:05

score 0 · Answer 3 · answered Dec 09 '13 at 09:39

0

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.

answered Dec 09 '13 at 09:39

Rune Grimstad

35,612
10
61
76

Compare C# Date with SQL Date within a Query

3 Answers3