Convert string to datetime value in LINQ

Question

Suppose I have a table storing a list of datetime (yyyyMMdd) in String format. How could I extract them and convert them into DateTime format dd/MM/yyyy ?

e.g. 20120101 -> 01/01/2012

I have tried the following:

var query = from tb in db.tb1 select new { dtNew = DateTime.ParseExact(tb.dt, "dd/MM/yyyy", null); };

But it turns out the error saying that the ParseExact function cannot be recgonized.

Solution 1

It's probably worth just doing the parsing locally instead of in the database, via AsEnumerable:

var query = db.tb1.Select(tb => tb.dt)
                  .AsEnumerable() // Do the rest of the processing locally
                  .Select(x => DateTime.ParseExact(x, "yyyyMMdd",
                                                CultureInfo.InvariantCulture));

The initial select is to ensure that only the relevant column is fetched, rather than the whole entity (only for most of it to be discarded). I've also avoided using an anonymous type as there seems to be no point to it here.

Note how I've specified the invariant culture by the way - you almost certainly don't want to just use the current culture. And I've changed the pattern used for parsing, as it sounds like your source data is in yyyyMMdd format.

Of course, if at all possible you should change the database schema to store date values in a date-based column, rather than as text.

Solution 2

As already said, it's better to store date as date-type column in your DB, but if you want just to convert strings from one format to another, you can do this:

db.tb1.Select(x => String.Format("{0}/{1}/{2}", x.Substring(6, 2), x.Substring(4, 2), x.Substring(0, 4))

Solution 3

Create a UDF in SQL Server and then import to your linq to sql project and use in the comparison

-- =============================================
-- Author:      
-- Create date: 
-- Description: Convert varchar to date
-- SELECT dbo.VarCharAsDate('11 May 2016 09:00')
-- =============================================
CREATE FUNCTION VarCharAsDate
(
    -- Add the parameters for the function here
    @DateAsVarchar NVarchar(100)
)
RETURNS DateTime
AS
BEGIN
    -- Declare the return variable here

    if IsDate(@DateAsVarchar) = 1 BEGIN
        -- Return the result of the function
        RETURN convert(datetime, @DateAsVarchar, 109)
    END
    RETURN NULL
END
GO

then in code

.Where(p => ValueDateTime > db.VarCharAsDate(p.Value))