CODEWITHSUNDEEP
c#asp.netlinq
amir
amir

Reputation: 25

Linq query to Get Data between two Dates

The date and time are saved as : 1397/06/10 13:55:49

Linq query is :

var showFamily = (from b in database.tbl_kharidnaghdi

                          join u in database.tbl_Customer on b.CustomerID_FK equals u.CustomerID
                          join c in database.tbl_User on u.UserID_FK equals c.UserID
                          orderby b.KharidnaghdiID descending

                          where u.UserID_FK == userid
                            && txtFamily.Text.Contains(u.Family)
                            &&string.Compare(b.Date, txtDate1.Text) <= 0  
                            && string.Compare(b.Date, txtDate2.Text) >= 0
                          select new
                          {
                              u.Name,
                              u.Family,
                              u.Mobile,
                              u.Price,
                              u.CustomerID,
                              b.KharidnaghdiID,
                              b.EtebarHadiye,
                              b.Cashier,
                              b.Date,
                          }).ToList();
        grdShowListUser.DataSourceID = null;
        grdShowListUser.DataSource = showFamily;

I think that the date should be separated from the clock like this : 1397/06/10

Upvotes: 0

Views: 2512

Answers (2)

ManishM
ManishM

Reputation: 593

data in database is 1397/06/10 13:55:49 data in txtdate is 1397/06/10

I am assuming 1397 is the year (yyyy), 06 is the month (MM) and 10 is the day (dd). And data in your Db is also in string format

DateTime date1 = DateTime.ParseExact(txtDate1.Text, "yyyy/MM/dd", CultureInfo.InvariantCulture);
DateTime date2 = DateTime.ParseExact(txtDate2.Text, "yyyy/MM/dd", CultureInfo.InvariantCulture);


var showFamily = (from b in database.tbl_kharidnaghdi
                  join u in database.tbl_Customer on b.CustomerID_FK equals u.CustomerID
                  join c in database.tbl_User on u.UserID_FK equals c.UserID
                  orderby b.KharidnaghdiID descending
                  where u.UserID_FK == userid
                  && txtFamily.Text.Contains(u.Family)
                  && DateTime.ParseExact(b.Date, "yyyy/MM/dd hh:mm:ss", CultureInfo.InvariantCulture) < date1
                  && DateTime.ParseExact(b.Date, "yyyy/MM/dd hh:mm:ss", CultureInfo.InvariantCulture) >= date2
                  select new
                  {
                      u.Name,
                      u.Family,
                      u.Mobile,
                      u.Price,
                      u.CustomerID,
                      b.KharidnaghdiID,
                      b.EtebarHadiye,
                      b.Cashier,
                      b.Date,
                  }).ToList();

Note: If the result is coming null, then please alter the greater than, less than condition and check

Upvotes: 0

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

Since you want to compare date values, it is necessary to convert them using DateTime.ParseExact() method from Text property of textboxes (which is string property) and use CompareTo() to compare between them:

DateTime date1 = DateTime.ParseExact(txtDate1.Text, "yyyy/MM/dd hh:mm:ss", CultureInfo.InvariantCulture);
DateTime date2 = DateTime.ParseExact(txtDate2.Text, "yyyy/MM/dd hh:mm:ss", CultureInfo.InvariantCulture);

var showFamily = (from b in database.tbl_kharidnaghdi
                  join u in database.tbl_Customer on b.CustomerID_FK equals u.CustomerID
                  join c in database.tbl_User on u.UserID_FK equals c.UserID
                  orderby b.KharidnaghdiID descending
                  where u.UserID_FK == userid
                  && txtFamily.Text.Contains(u.Family)
                  && b.Date.CompareTo(date1.Date) <= 0 // date comparison here
                  && b.Date.CompareTo(date2.Date) >= 0
                  select new
                  {
                      u.Name,
                      u.Family,
                      u.Mobile,
                      u.Price,
                      u.CustomerID,
                      b.KharidnaghdiID,
                      b.EtebarHadiye,
                      b.Cashier,
                      b.Date,
                  }).ToList();

Note that if you're using LINQ to Entities, the Date property may not supported, you can use DbFunctions.TruncateTime() or project it to LINQ to Objects with ToList() or AsEnumerable() as alternative.

Upvotes: 1

Related Questions