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

views:

1072

answers:

+2 Q:

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

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

+1 A:

Obalix 2010-03-11 19:55:06

Date is not nullable.Also, ToString doesn't work with linq2sql on datetime.

Bogdan Maxim 2010-03-11 20:09:31

It is not doable because the rowcount is very high.

Bogdan Maxim 2010-03-11 20:30:34

Then use the second option I inserted.

Obalix 2010-03-11 20:41: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()??

Hightechrider 2010-03-11 21:00:14

@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.

Obalix 2010-03-11 21:04:12

+1 A:

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.

Neil T. 2010-03-11 20:55:15

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 2010-03-11 21:12:21

Can I see the structures?

Neil T. 2010-03-12 00:16:16

ansaurus

tags:

views:

answers:

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

related questions