Override Linq-to-Sql Datetime.ToString() Default Convert Values
- by snmcdonald
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;
}
}
}
