Decrease Font Size
Increase Font Size
   BLOG

IQueryable datetime format

IQueryable datetime format yyyy/MM/dd|

Introduction

Consider this scenario, you have an entity model and one of the property is DateTime type. In the IQueryable query, you want to select that property as string with format 2017/01/09 into a ViewModel class.

A quick thought is to employ the ToString("yyyy-MM-dd") method. But during run time the code throws the error "LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.".

After googling around the web, I found the problem could be solved by using the combination of SqlFunctions.Replicate, SqlFunctions.StringConvert and SqlFunctions.DateName. Refer to listing 1.

Listing 1

 IQueryable yourModel =
                from p in db.Table1
                select new TestObjectViewModel
                {
                    Id = p.Id,
                    Text = p.Text,
                    CreatedDateString = SqlFunctions.DateName("year", p.CreatedDate) + "/" +
                        SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)p.CreatedDate.Month).TrimStart().Length) +
                        SqlFunctions.StringConvert((double)p.CreatedDate.Month).TrimStart() + "/" +
                        SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", p.CreatedDate).Trim().Length) +
                        SqlFunctions.DateName("dd", p.CreatedDate).Trim()
                };

What if the property is nullable datetime type? The codes are about the same except a little longer and the Month is accessible through the Value property. The code in listing 2 will display the UpdatedDateString value in yyyy/MM/dd format and empty string if the property value is null.

Listing 2

        IQueryable yourModel =
                from p in db.Table1
                select new TestObjectViewModel
                {
                    Id = p.Id,
                    Text = p.Text,
                    UpdatedDateString = SqlFunctions.DateName("year", p.UpdatedDate) +
                        SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)p.UpdatedDate.Value.Month).TrimStart().Length) +
                        SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)p.UpdatedDate.Value.Month).TrimStart().Length) +
                        SqlFunctions.StringConvert((double)p.UpdatedDate.Value.Month).TrimStart() +
                        SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)p.UpdatedDate.Value.Month).TrimStart().Length) +
                        SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", p.UpdatedDate).Trim().Length) +
                        SqlFunctions.DateName("dd", p.UpdatedDate).Trim()
                };

Conclusion

There are others alternative way to approach this issue, I’m not suggesting this is the only solution. Just want to share my finding with the community.

Resources

https://msdn.microsoft.com/en-us/library/ms174395.aspx