Linq 2 Sql DateTime format to string yyyy-MM-dd

Question

Basically, i need the equivalent of T-SQL CONVERT(NVARCHAR(10), datevalue, 126)

I've tried:

from t in ctx.table select t.Date.ToString("yyyy-MM-dd") but it throws not supported exception
from t in ctx.table select "" + t.Date.Year + "-" + t.Date.Month + "-" + t.Date.Day but i don't think it's an usable solution, because i might need to be able to change the format.

~~The only option I see is to use Convert.ToString(t.Date, FormatProvider), but i need a format provider, and I'm not sure it works either~~

FormatProvider doesn't work, String.Format doesn't work (string.Format("{0:yyyy-MM-dd}", t.Date) throws not supported exception too).

Below solution worked for me. Please chek below link http://stackoverflow.com/questions/7999771/convert-datetime-to-a-formatted-string-inside-a-linq-to-sql-query — Saiful Haque Sumon, May 28 '16 at 19:09

score 7 · Answer 1 · edited Jul 26 '12 at 19:31

7

In case someone else has this problem, I solved this problem by creating a seperate function to do the date formatting for me.

My Linq looks something like this:

from a in context.table
where ...
select new Class
{
DateString = GetFormattedString(a.DateTimeObject)
}

And GetFormattedString just returns DateTimeObject.ToString("yyyy-MM-dd"); This works for me!

edited Jul 26 '12 at 19:31

BNL

7,085
4
27
32

answered Feb 28 '11 at 12:48

amcdrmtt

1,253
12
13

I agree. Although there are several ways to do do the conversion after the query results come in, passing the nullable result to a method seems the most pretty solution to me. +1 – Patrick de Kleijn Aug 11 '11 at 12:18
1

Much nicer than just for this purpose casting whole object toList. – Saulius Mar 06 '14 at 07:21

score 6 · Answer 2 · edited May 26 '21 at 18:47

6

~~Assuming that t.Date is nullable (DateTime?) this could be the problem, try using:~~

from t in ctx.table select (t.HasValue ? t.Date.Value.ToString("yyyy-MM-dd") : string.Empty );

Edit: Second try

The problem is the translation to SQL; it tries to translate the .ToString() to an SQL representation, and fails. So if you should do the following it should work:

(from t in ctx.table select t.Date).ToList().Select(d => d.ToString("yyyy-MM-dd"))

Or

(from t in ctx.table select t.Date).AsEnumerable().Select(d => d.ToString("yyyy-MM-dd"))

AsEnumerable() transforms the previously used IQueryable into an IEnumerable, thus stopping the generation of the SQL (in case of Linq to SQL) or any other transfromation by the provider implementing the specific IQueryable (e.g. Linq to SQL Provider).

Note, before calling AsEnumerable() you should have completed any actions that you want to be converted to SQL and executed on the database directly.

edited May 26 '21 at 18:47

Andrew

307
7
13

answered Mar 11 '10 at 19:55

AxelEckenberger

16,628
3
48
70

Date is not nullable. Also, ToString doesn't work with linq2sql on datetime. – Bogdan Maxim Mar 11 '10 at 20:09
It is not doable because the rowcount is very high. – Bogdan Maxim Mar 11 '10 at 20:30
Then use the second option I inserted. – AxelEckenberger Mar 11 '10 at 20:41
2nd option is the same in terms of how many rows are returned. But if the rowcount is very high you have other issues .. why are you fetching so many rows? Either you need them all, and need them formatted like this, or you don't need them all in which case you should filter before you project. Maybe you should be paging the results back from SQL using Skip() and Take()?? – Ian Mercer Mar 11 '10 at 21:00
@Hightechrider: The first option enumerates the items on the `ToList()` thus pulling all lines. The second option does not enumerate, so adding additional Linq operation is possible, further filtering the result set before eventually enumerating the result. But, you are right ... why not filter on the DB or do the conversion when it is needed, e.g. for output. – AxelEckenberger Mar 11 '10 at 21:04

score 5 · Answer 3 · answered Mar 11 '10 at 20:55

5

Is there a reason to perform the conversion on the database side? Whenever I run into this type of situation, I tend to just allow the database to give me the raw data and then do the massaging and manipulation within the application. Depending on the volume of requests to the database server and the size of the result set, I don't want to tie up processing and response time doing data conversions that can be handled by the client.

answered Mar 11 '10 at 20:55

Neil T.

3,308
1
25
30

I need to run some string matching: I have a string composed of some `nvarchar` columns and a `datetime` column, and i need to match it with a column in another table. – Bogdan Maxim Mar 11 '10 at 21:12

score 3 · Answer 4 · edited May 23 '17 at 11:46

3

look no.3

var user = (from u in users
                     select new
                     {
                         name = u.name,
                         birthday = u.birthday.Value
                     })
                     .ToList()
                     .Select(x => new User()
                     {
                         name = x.name,
                         birthday = x.birthday.ToString("yyyyMMdd") // 0埋めされるよ
                     });

edited May 23 '17 at 11:46

Community

1
1

answered Oct 24 '14 at 06:14

Kotaro Inoue

126
4

2

This worked for me. Very clear what its doing, and flexible. – seagulledge Feb 16 '19 at 20:01

score 1 · Answer 5 · answered Apr 22 '13 at 09:43

Try to create an object class that you can set the properties and let the properties be the value for your view.. Set the datetime data from LINQ as string and not datetime.. ex.

//Property class
[DataContract()]
public class Info
{
[DataMember(Name = "created_date")]
public string CreateDate;
}


//Controller
var date = from p in dbContext.Person select p;
CreateDate = Convert.ToDateTime(p.create_date).ToString("yyyy-MM-dd");

Hope you'll try this.. I have this on my past applications and this is what I did.

Carel · Answer 6 · 2021-10-21T12:55:28.993

I had a global search function on a quoting website from which I wanted to be able to search on all details of a quote (quote references, vehicle details, customer details etc.) including the created dates using only the single input text value:

This means that I definitely don't want to enumerate the results before attempting to cast the date to the appropriate string format.

In an attempt to do this, I've come up with the following:

// this is obviously only a fragment of my actual query
_context.Quotes
        .Where(q => string.Concat(
                      q.DateCreatedUtc.Day < 10 ? "0" : "", 
                      q.DateCreatedUtc.Day, 
                      "/", 
                      q.DateCreatedUtc.Month < 10 ? "0" : "", 
                      q.DateCreatedUtc.Month, 
                      "/", 
                      q.DateCreatedUtc.Year
                    )
                    .Contains(searchTerm));

I can confirm this translates to a database operation using EF Core V5 and Pomelo V5 with a MySql database.

The generated SQL looks something like this:

WHERE
    LOCATE(@search_term, CONCAT(
        CASE WHEN EXTRACT(DAY FROM `quote`.`date_created_utc`) < 10 THEN '0' ELSE '' END,
        CAST(EXTRACT(DAY FROM `quote`.`date_created_utc`) AS CHAR),
        '/',
        CASE WHEN EXTRACT(MONTH FROM `quote`.`date_created_utc`) < 10 THEN '0' ELSE '' END,
        CAST(EXTRACT(MONTH FROM `quote`.`date_created_utc`) AS CHAR),
        '/',
        CAST(EXTRACT(YEAR FROM `quote`.`date_created_utc`) AS CHAR)
    )) > 0

This entire query has turned into a bit of a Frankenstein though and I am seriously questioning the value of allowing users to search on the dates.

score 0 · Answer 7 · answered Apr 27 '23 at 20:57

Having stumbled on this thread while chasing this exact problem, EF (at least EF core) has an elegant solution: .Where(x => x.DateField.Date.ToString() ...) [where you can do a .Contains or whatever you want with that, and it all happens on server-side. the .Date.ToString() converts to the expected yyyy-mm-dd format.

score -1 · Answer 8 · edited Aug 25 '16 at 20:56

-1

try this

var select = from s in db.Table
             where s.date == someDate
             select new 
             {
                date = DateTime.Parse(s.date.ToString()).ToString("yyyy-MM-dd"),
             };

edited Aug 25 '16 at 20:56

CloudPotato

1,255
1
17
32

answered Aug 25 '16 at 19:58

Guru

1

it actually works in linq2sql but will not work in EF throws LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method.. – Jack0fshad0ws Oct 11 '16 at 03:56

Linq 2 Sql DateTime format to string yyyy-MM-dd

8 Answers8

Linked