Shepwedd
asked on
How do I trim a datetime value to just its date value in LINQ?
I have created a c# website using visual studio 2008. I am using the asp.net 3.5 listview control as a frontend to my backend sql server 2005 database. I am using a linq data source (linq to sql) to bind my listview to my backend database. Within the OnSelecting event of my linq data source I have written the attached query which works fine in regards to datetime values that do not allow nulls in my database but unfortunately it was necessary for me to allow nulls in one of my datetime fields (StopDate) which has invertingly not allowed me to reference "ToShortDateString" for that field in my linq query? Is there a way around this at all?
var query = from ptp in db.TrustPayments
join ppfl in db.PaymentFrequencyLookups on ptp.FrequencyCode equals ppfl.FrequencyCode
join tt in db.Trusts on ptp.TrustID equals tt.TrustID
select new { ptp.PaymentID, ptp.TrustID, StopDate = ptp.StopDate.ToShortDateString(), tt.ClientName, tt.MatterNo, ptp.SWDeptAccountNo, ptp.FrequencyCode, ptp.Amount, ptp.PayeeName, ptp.PayeeAddress, ptp.BankName, ptp.BankAddress, ptp.AccountName, ptp.AccountNo, ptp.SortCode, ptp.PaymentNotes, ptp.Archive };
ASKER
After trying your suggested code I noticed that Intelisense didn't offer me "ToShortDateString()" and when I do type it in I get the error: "System.Nullable<System.Da teTime>" does not contain a definition for "ToShortDateString" ...?
Interesting. So you need to cast it. I tried this snippet and it works with and w/o a date.
System.DateTime? date = null;//System.DateTime.Now ;
Console.WriteLine((date != null) ? ((DateTime)date).ToShortDa teString() : "");
System.DateTime? date = null;//System.DateTime.Now
Console.WriteLine((date != null) ? ((DateTime)date).ToShortDa
Hi Shepwedd;
Use a conditional if statement to test for null and take the proper action. See code snippet.
Fernando
Use a conditional if statement to test for null and take the proper action. See code snippet.
Fernando
var query = from ptp in db.TrustPayments
join ppfl in db.PaymentFrequencyLookups on ptp.FrequencyCode equals ppfl.FrequencyCode
join tt in db.Trusts on ptp.TrustID equals tt.TrustID
select new { ptp.PaymentID,
ptp.TrustID,
StopDate = if (ptp.StopDate != null) ? ptp.StopDate.ToShortDateString() : String.Empty ,
tt.ClientName,
tt.MatterNo,
ptp.SWDeptAccountNo,
ptp.FrequencyCode,
ptp.Amount,
ptp.PayeeName,
ptp.PayeeAddress,
ptp.BankName,
ptp.BankAddress,
ptp.AccountName,
ptp.AccountNo,
ptp.SortCode,
ptp.PaymentNotes,
ptp.Archive };
We went through that attempt. See my first response. My second post should solve the problem.
The 1st attempt seems to be missing the keyword "if"
@ ToddBeaulieu; My bad "if" not part of the conditional if statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem, though, is that SQL Server has no concept of DateTime.ToShortDateString , so the conversion to SQL fails
As FernandoSoto his query works fine.
Also you can do by:
Eg:
As FernandoSoto his query works fine.
Also you can do by:
Eg:
var list = (from p in Products select p.SellEndDate).ToList();// calling ToList() above means we have the entire resultset in memory and// no longer have to pass the query back to SQL Servervar stuff = from p in list select new{ selldate = p.SellEndDate == null ? string.Empty : p.SellEndDate.Value.ToShortDateString()};
var list = (from p in Products
select p.SellEndDate).ToList();
// calling ToList() above means we have the entire resultset in memory and
// no longer have to pass the query back to SQL Server
var stuff = from p in list select new
{
selldate = p.SellEndDate == null ?
string.Empty :
p.SellEndDate.Value.ToShor tDateStrin g()
};
select p.SellEndDate).ToList();
// calling ToList() above means we have the entire resultset in memory and
// no longer have to pass the query back to SQL Server
var stuff = from p in list select new
{
selldate = p.SellEndDate == null ?
string.Empty :
p.SellEndDate.Value.ToShor
};
ASKER
Excelllent, this worked a treat! Just for reference though, your initial code suggestion that included the "if" didn't work.
To your statement, "your initial code suggestion that included the "if" didn't work.", correct as I stated that on post 05/18/09 11:11 PM, ID: 24418184, Writing code in both C# and VB .Net, the syntax gets blured sometimes.
what i found most interesting was that the final solution was exactly the same as the solution i posted 5 entries prior! no biggie, i see a lot of that here.
@ ToddBeaulieu;
Not exactly the same. Please note that first I set a range variable called Date to the conditional if statement outside the Select clause once that is done I use that range variable in the Select clause. The compiler has issues with translating the query to T-SQL when done the other way around
This line was added to the query:
let Date = (ptp.StopDate.Value != null) ? ptp.StopDate.Value.ToShort DateString () : String.Empty
This line uses the result of the above line:
StopDate = Date ,
Which is not the same as:
StopDate = (ptp.StopDate != null) ? ptp.StopDate.ToShortDateSt ring() : null
Fernando
Not exactly the same. Please note that first I set a range variable called Date to the conditional if statement outside the Select clause once that is done I use that range variable in the Select clause. The compiler has issues with translating the query to T-SQL when done the other way around
This line was added to the query:
let Date = (ptp.StopDate.Value != null) ? ptp.StopDate.Value.ToShort
This line uses the result of the above line:
StopDate = Date ,
Which is not the same as:
StopDate = (ptp.StopDate != null) ? ptp.StopDate.ToShortDateSt
Fernando
StopDate = (ptp.StopDate != null) ? ptp.StopDate.ToShortDateSt