locked
Checking for null in my date with SqlFunctions. RRS feed

  • Question

  • User457850011 posted

    I want to check for null in my date function. If null show blank else show Date. I am doing like this but is not not working when null. When null it is showing //

    StartDate = SqlFunctions.DateName("day", x.StartDate).Trim() + "/" + SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", x.StartDate) == null ? SqlFunctions.DateName("day", x.StartDate).Trim() + "/" + SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", x.StartDate) : string.Empty,

    Tuesday, October 2, 2018 4:57 PM

Answers

  • User457850011 posted

    Hi 

    Thanks. I am able to format it now and is working correctly. Thanks

    StartDate = x.StartDate == null ? string.Empty : SqlFunctions.DateName("day", x.StartDate).Trim() + "/"
    + SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" +

    SqlFunctions.DateName("year", x.StartDate),

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2018 6:59 PM

All replies

  • User753101303 posted

    Hi,

    It seems to be done on the client side even though using SqlFunctions ? Formatting is best done on the client side when you are about to show the value to a user.

    You could use https://msdn.microsoft.com/en-us/library/bz9tc508.aspx to force the culture you want and then use :

    StartDate=x.StartDate==null?string.Empty:x.StartDate.ToString("d");

    If doing the extra step of using the fluent API or data annotations to mark the property you just have nothing else to do then and it should just work.

    Tuesday, October 2, 2018 5:19 PM
  • User457850011 posted

    Hi Pat

    Thanks for your response. I am exporting to excel and need to format the Date and check for nulls. Right now my issue is how to remove the // after showing null values

    Tuesday, October 2, 2018 5:45 PM
  • User475983607 posted

    Use an if...

    if(x.StartDate != null)
    {
     // do custom formatting
    }

    Tuesday, October 2, 2018 5:52 PM
  • User457850011 posted

    The query is a Linq query.It is leaving // behind when null

    var excelExport = TestDatatABLE
    .Include(x => x.admin)
    .Include(x => x.employees)

    .Where(x => x.Id == 106

    .Select(x => new
    {


    StartDate = SqlFunctions.DateName("day", x.StartDate).Trim() + "/" +
    SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" +
    SqlFunctions.DateName("year", x.StartDate) == null ?
    SqlFunctions.DateName("day", x.StartDate).Trim() + "/" +
    SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" +
    SqlFunctions.DateName("year", x.StartDate) : string.Empty

    }

    Tuesday, October 2, 2018 5:58 PM
  • User475983607 posted

    Try using standard DateTime format strings.

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    var excelExport = TestDatatABLE
    .Include(x => x.admin)
    .Include(x => x.employees)
    
    .Where(x => x.Id == 106
    
    .Select(x => new
    {
    	StartDate = x.StartDate == null ? string.Empty : x.StartDate.ToString("dd/MM/yyyy")
    }

    Tuesday, October 2, 2018 6:05 PM
  • User457850011 posted

    At least it works without formatting the date. Getting an error when trying to format the Date by calling x.StartDate.ToString("dd/MM/yyyy")

    But I need to format the DateTime to get only the Date portion

    LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

    Tuesday, October 2, 2018 6:29 PM
  • User475983607 posted

    denkyira

    At least it works without formatting the date. Getting an error when trying to format the Date by calling x.StartDate.ToString("dd/MM/yyyy")

    But I need to format the DateTime to get only the Date portion

    LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

    For got about that.  Just do the formatting outside the after the linq query.  Perhaps when building the Excel output.

    Tuesday, October 2, 2018 6:31 PM
  • User457850011 posted

    Hi 

    Thanks. I am able to format it now and is working correctly. Thanks

    StartDate = x.StartDate == null ? string.Empty : SqlFunctions.DateName("day", x.StartDate).Trim() + "/"
    + SqlFunctions.StringConvert((double)x.StartDate.Value.Month).TrimStart() + "/" +

    SqlFunctions.DateName("year", x.StartDate),

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2018 6:59 PM