Solved

linq to sql not between

Posted on 2013-01-31
7
479 Views
Last Modified: 2013-01-31
I have the following where clausing in sql, how do i write the comparable in linq?

where CONVERT(DATE, GETDATE()) not between CheckInDate and  CheckOutDate

I'm trying to query the non active records in my table, which means today's date will not fall in between the check in and check out dates.

Thanks
0
Comment
Question by:michael1174
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38838930
I assumed CheckOutDate and CheckInDate are properties of a data structure (list/array/enumerable etc) the where applies to:

.Where(n=>(DateTime.Now >  n.CheckOutDate) && (DateTime.Now < n.CheckInDate))

Open in new window

0
 
LVL 2

Author Comment

by:michael1174
ID: 38838945
Hi thanks I already have that for active records, I need to get NOT active records which is a NOT between.
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 38838959
sorry:

.Where(n=>(DateTime.Now >  n.CheckOutDate) || (DateTime.Now < n.CheckInDate))

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:michael1174
ID: 38839034
one more question, it worked for one of my queries, but my other query, which first i am getting it through a SP:

var query = (get from sp).toList();

   return query.Where(m => (DateTime.Today >= m.StartDate) &&
                        (m.EndDate <= DateTime.Today));

it doesn't seem to be filtering out the records, see any problems?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38839231
this query returns all records between StartDate and EndDate (exclusive compare) , is that what u want?

btw, you used DateTime.Today and not DateTime.Now to compare which returns the current date with time  equals to 12:00:00 AM.

for example:

DateTime.Today -> {1/31/2013 12:00:00 AM}
DateTime.Now -> {1/31/2013 14:45:00 PM}
0
 
LVL 2

Author Comment

by:michael1174
ID: 38839307
yes, that is what I want but for some reason my query result is wrong.  It doesn't seem like the filter was applied. I'm first calling a sp, and then applying that where.  The method returns a ienumerable and the result is bound to a grid.

thanks for explaination on the date today vs date now.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38839313
well the query is simple and straight forward.
debug the StartDate and EndDate and see if it falls within the query range.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 23
Linked Server - SP with Param to VIew 7 19
SQL Get Store Procedure Column Name As Row 5 39
Nested forach loop to linq 3 24
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 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