Override Linq-to-Sql Datetime.ToString() Default Convert Values

Posted by snmcdonald on Stack Overflow See other posts from Stack Overflow or by snmcdonald
Published on 2011-02-08T00:33:36Z Indexed on 2011/02/26 23:25 UTC
Read the original article Hit count: 308

Is it possible to override the default CONVERT style? I would like the default CONVERT function to always return ISO8601 style 126.

Steps To Reproduce:

DROP TABLE DATES;

CREATE TABLE DATES
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    MYDATE DATETIME DEFAULT(GETUTCDATE())
);

INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;

SELECT CONVERT(NVARCHAR,MYDATE) AS CONVERTED, 
       CONVERT(NVARCHAR(4000),MYDATE,126) AS ISO, 
       MYDATE FROM DATES
WHERE MYDATE LIKE'Feb%'

Output:

CONVERTED                   ISO                          MYDATE
--------------------------- ---------------------------- -----------------------
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040

Linq-to-Sql calls CONVERT(NVARCHAR,@p) when I cast ToString(). However, I am displaying all my data in the ISO8601 format. I would like to override the database default if possible to CONVERT(NVARCHAR,@p,126).

I am using Dynamic Linq-to-Sql as demoed by ScottGu to process my data.

PropertyInfo piField = typeof(T).GetProperty(rule.field);
if (piField != null)
{
    Type typeField = piField.PropertyType;
    if (typeField.IsGenericType && typeField.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
    {
        filter = filter
           .Select(x => x)
           .Where(string.Format("{0} != null", rule.field))
           .Where(string.Format("{0}.Value.ToString().Contains(\"{1}\")", rule.field, rule.data));
    }
    else
    {
        filter = filter
            .Select(x => x)
            .Where(string.Format("{0} != null", rule.field))
            .Where(string.Format("{0}.ToString().Contains(\"{1}\")", rule.field, rule.data));
    }
} 

I was hoping my property would convert the expression from CONVERT(NVARCHAR,@p) to CONVERT(NVARCHAR,@p,126), however I get a NotSupportedException: ... has no supported translation to SQL.

public string IsoDate
{
    get
    {
        if (SUBMIT_DATE.HasValue)
        {
            return SUBMIT_DATE.Value.ToString("o");
        }
        else
        {
            return string.Empty;
        }
    }
}

© Stack Overflow or respective owner

Related posts about c#

Related posts about sql-server