SqlFunctions convert string to date

Question

I'm working on a project and i have problem with filtering records by date. I have a generic method to search the records of any entities,my searchQuery is like '2014-03-15' and the records datetime format is '2014-03-15 00:00:00.000', My sql query should convert the datetime sql records to date format only and then compare it with my query, The problem is this that the genereated date from records are not eqauls to my query, I'm using the datepart function then concating the string result, but the result will be like ' 2014- 3- 15' and cause no matching, if a way exist to cast the string to date in sqlfunctions that would be great, Here is my code

                var date = Expression.Convert(propExp, typeof(Nullable<DateTime>));                       
                var datePart = typeof(SqlFunctions).GetMethod("DatePart",
                    new Type[] { typeof(string), typeof(Nullable<DateTime>) });   

                var month = Expression.Call(datePart, Expression.Constant("MM"), date);                   
                var toDouble = Expression.Convert(month, typeof(Nullable<double>));                      
                var monthPartToString = typeof(SqlFunctions).GetMethod("StringConvert",
                    new Type[] { typeof(Nullable<double>) });                   
                var monthPart = Expression.Call(monthPartToString, toDouble);


                var dd = Expression.Call(datePart, Expression.Constant("dd"), date);                      
                var toDoubledd = Expression.Convert(dd, typeof(Nullable<double>));                      
                var ddPartToString = typeof(SqlFunctions).GetMethod("StringConvert",
                    new Type[] { typeof(Nullable<double>) });                      
                var ddPart = Expression.Call(ddPartToString, toDoubledd);


                var yy = Expression.Call(datePart, Expression.Constant("yyyy"), date);                  
                var toDoubleyy = Expression.Convert(yy, typeof(Nullable<double>));                   
                var yyPartToString = typeof(SqlFunctions).GetMethod("StringConvert",
                    new Type[] { typeof(Nullable<double>) });                    
                var yyPart = Expression.Call(yyPartToString, toDoubleyy);


                var conCat4 = typeof(string).GetMethod("Concat",
            new Type[] { typeof(string), typeof(string), typeof(string), typeof(string) });
                var conCat2 = typeof(string).GetMethod("Concat",
                    new Type[] { typeof(string), typeof(string) });
                var delim = Expression.Constant("-");

                stringProp = Expression.Call(conCat2, Expression.Call(conCat4, yyPart, delim, monthPart, delim), ddPart);

the genereated sql is

 select * from orders  where STR( CAST( DATEPART(yyyy, CreatedOn) AS float)) + N'-' + STR( CAST( DATEPART(MM,CreatedOn) AS float)) + N'-' + STR( CAST( DATEPART(dd, CreatedOn ) AS float))  LIKE N'%2014-03-15%'

I need a way to cast this string to date by sqlFunctions

How about this: http://stackoverflow.com/questions/10304373/how-convert-string-to-date-t-sql — ganders, Apr 11 '14 at 13:47
To clarify : you have a datetime in your db (stored as a datetime), and you wanna compare this with a string (given as parameter, not in db) containing only the "Date" part ? — Raphaël Althaus, Apr 11 '14 at 14:38
Yes, But the main problem is this that i should convert the date string to sql date type by sql functions and there is no any function to do this,I'm using reflection to build the query and this make the problem harder to solve — user3333426, Apr 11 '14 at 15:33

score -1 · Answer 1 · answered Apr 11 '14 at 13:48

-1

Don't use strings to compare dates. EVER. Instead, remember that as of Sql Server 2008 there is a Date type that only stores the date information, with no time component. You should generate sql that converts both sides of the comparison into that type, like this:

select * from orders where cast(CreatedOn As Date) = @MyDate

Where @MyDate is already a parameter that you will include in the query that has an explicit Date type. Never generate queries that have values substituted directly in the query.

answered Apr 11 '14 at 13:48

Joel Coehoorn

399,467
113
570
794

here are two problems 1=> i don't want to use date to store records coz i need it in other queries 2=> I know if i use cast as date the problem will solved but i'm using sqlfunction and there is no option to use this function, I need a way to cast the string to date by sqlFunctions – user3333426 Apr 11 '14 at 13:51

SqlFunctions convert string to date

1 Answers1