Solved

How do I trim a datetime value to just its date value in LINQ?

Posted on 2009-05-18
14
584 Views
Last Modified: 2013-11-11
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 };

Open in new window

0
Comment
Question by:Shepwedd
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24413733
I wonder if you can just use inline "if" syntax:

StopDate = (ptp.StopDate != null) ? ptp.StopDate.ToShortDateString() : null
0
 

Author Comment

by:Shepwedd
ID: 24413920
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.DateTime>" does not contain a definition for "ToShortDateString" ...?
0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24414126
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).ToShortDateString() : "");
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24416277
Hi Shepwedd;

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 };

            

Open in new window

0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24416303
We went through that attempt. See my first response. My second post should solve the problem.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24416377
The 1st attempt seems to be missing the keyword "if"
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24418184
@ ToddBeaulieu; My bad "if" not part of the conditional if statement.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 24418367
Hi Shepwedd;

This version of the query will do what you need.

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

            let Date = (ptp.StopDate.Value != null) ? ptp.StopDate.Value.ToShortDateString() : String.Empty

            select new { ptp.PaymentID, 

                         ptp.TrustID, 

                         StopDate = Date ,

                         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 

                       };

Open in new window

0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24418591
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:
 

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()};

Open in new window

0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24418595
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.ToShortDateString()
};
0
 

Author Closing Comment

by:Shepwedd
ID: 31582665
Excelllent, this worked a treat! Just for reference though, your initial code suggestion that included the "if" didn't work.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24421478
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.
0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24421534
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.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24421811
@ 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.ToShortDateString() : 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.ToShortDateString() : null

Fernando
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now