locked
Date format in LINQ RRS feed

  • Question

  • User126547376 posted

    HI

    select CONVERT(varchar(50),ApprovedDate,105) from srpEMployee

    here is SQL Query to Convert date into DD-MM-YYYY, like this wat is query used in LINQ to convert this format.

    i am using VS2008 , SQL 2008


    Help Me

    Tuesday, July 28, 2009 4:57 AM

Answers

All replies

  • User-1507865547 posted

    Hey, can you show us the code what exactly are you looking for? If you are looking for converting the date into a particular formatted string then it is the same as in .NET. 

    i.e. DateTime.ToString("dd-MM-yyyy")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2009 5:07 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2009 5:09 AM
  • User126547376 posted

    var results = from yc in oDC.GetTable<srpYearCalendar>()
                                  where yc.yclCompanyCode == CmpCode
                                
                                  orderby yc.yclHolidayDt
                                  select new
                                  {
                                      CalenderID = yc.yclCalendarID,
                                      //HolidayDt = yc.yclHolidayDt,
                                      HolidayDt = DateTime.Parse(yc.yclHolidayDt.ToString("dd-MM-yyyy")),
                                      Reason = yc.yclReason
                                  
                                  };
    it will throw error like {System.NotSupportedException: Method 'System.String ToString(System.String)' has no supported translation to SQL



    this is my Query How to Set


    Help Me


    Tuesday, July 28, 2009 6:39 AM
  • User-1507865547 posted

    var results = from yc in oDC.GetTable<srpYearCalendar>()
                                  where yc.yclCompanyCode == CmpCode
                                
                                  orderby yc.yclHolidayDt
                                  select new
                                  {
                                      CalenderID = yc.yclCalendarID,
                                      HolidayDt = yc.yclHolidayDt,
                                      Reason = yc.yclReason
                                  
                                  };

    var newResults = from result in results.ToList()

                                 select

                                {

                                      CalenderID = result.CalendarID,
                                      HolidayDt = result.HolidayDt.ToString("dd-MM-yyyy"),
                                      Reason = result.Reason

                                };

    Hope, this will solve your problem. The expected output will be in newResult

    Tuesday, July 28, 2009 6:56 AM
  • User126547376 posted

    ================================================================
     private DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
            {
                if (query == null)
                {
                    throw new ArgumentNullException("query");
                }

                IDbCommand cmd = ctx.GetCommand(query as IQueryable);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = (SqlCommand)cmd;
                DataTable dt = new DataTable("sd");

                try
                {
                    cmd.Connection.Open();
                    adapter.FillSchema(dt, SchemaType.Source);
                    //--- to typeof(string) for all column
                    //DataColumnCollection dataColumns = dt.Columns;
                    //int columnCount = dataColumns.Count;
                    //foreach (DataColumn dataColumn in dataColumns)
                    //{
                    //    dataColumn.DataType = typeof(string);
                    //}
                    //---
                    adapter.Fill(dt);
                }
                finally
                {
                    cmd.Connection.Close();
                }
                return dt;
            }

    =======================================
    public DataTable SelectRecordByYear(string CmpCode, string StCode, int year, char EStatus, bool IsDeleted)
            {
                try
                {
                  
                    var results = from yc in oDC.GetTable<srpYearCalendar>()
                                  where yc.yclCompanyCode == CmpCode
                                 && yc.yclStateCode == StCode
                                            && yc.yclYear == year
                                            && yc.yclEntryStatus == EStatus
                                            && yc.yclDeleted == IsDeleted
                                  orderby yc.yclHolidayDt
                                  select new
                                  {
                                      CalenderID = yc.yclCalendarID,
                                      HolidayDt = yc.yclHolidayDt,
                                      Reason = yc.yclReason

                                  };

                    var newResults = from result in results.ToList()
                                     select new

                                     {

                                         CalenderID = result.CalenderID,
                                         HolidayDt = result.HolidayDt.ToString("dd-MM-yyyy"),
                                         Reason = result.Reason

                                     };

                    DataTable dt = ToDataTable(oDC, newResults);

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
    =================
    it will throw error like Value cannot be null.
    Parameter name: query

    this is My query it through exception in Datatable. Result not biniding in DataTable

    Help Me





    Tuesday, July 28, 2009 9:54 AM
  • User2011918074 posted

    Hi manojkiean, 

    From the description, I think the error is caused by the null value in result.HolidayDt.

    Please modify the code as follows:

    HolidayDt = result.HolidayDt==null?"Null Date" : result.HolidayDt.ToString("dd-MM-yyyy"),


    Related document:

    ?: Operator (C# Reference)
    http://msdn.microsoft.com/en-us/library/ty67wk28.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 31, 2009 2:45 AM
  • User-1475601915 posted

    Its not working.. Its giving me syntax error.

    Any Other solution to check null date?

    Thanks,

    Aarti 

    Wednesday, March 3, 2010 8:19 AM
  • User594131493 posted

    var Query = from emp in dc.employees 
                         join order in dc.Orders on emp.empid equals order.empid
                        where emp.salary >= 8000
                        orderby emp.empname
                        select new
                        {
                            Name = emp.empname,
                            Salary = emp.salary * 2,
                            Date =  order.OrderDate.ToShortDateString()
                        };

    Check this it's working fine

    var Query = from emp in dc.employees 

                         join order in dc.Orders on emp.empid equals order.empid

                        where emp.salary >= 8000

                        orderby emp.empname

                        select new

                        {

                            Name = emp.empname,

                            Salary = emp.salary * 2,

                            Date =  order.OrderDate.ToShortDateString()

                        };

    Wednesday, June 23, 2010 7:47 AM
  • User-913735801 posted

    DateTime.Now.ToString("a");

    Monday, August 22, 2011 2:37 PM