Solved

How do I search between dates (variables) using Visual Basic Linq to sql?

Posted on 2010-09-21
3
634 Views
Last Modified: 2013-11-11
I have two dates stored as strings:
dim x as string = '2010/9/9'
dim y as string = '2010/8/8'

I have the following LINQ TO SQL statement that works perfectly fine:
            Dim listTrans As List(Of TransactionData) = _
                (From trans In db.TransHeaderTables _
                Select New TransactionData With _
                    { _
                        .TransId = trans.TransId.ToString, _
                        .EncryptionTCN = trans.TCN, _
                        .EncryptionORI = trans.EncryptionORI _
                    }).ToList()

What I need is to add in a way to do a WHERE trans.CompletedDate between X and Y.  Can someone modify the statement above?  Also, I'd love a separate modified statement above that can grab the records within the last X days.

Thank you!
0
Comment
Question by:RichardRiga
3 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 33728131

' Between X and Y
Dim listTrans As List(Of TransactionData) = _
    (From trans In db.TransHeaderTables _
     Where trans.CompletedDate >= Convert.ToDateTime(x) AndAlso trans.CompletedDate <= Convert.ToDateTime(y)
    Select New TransactionData With _
        { _
            .TransId = trans.TransId.ToString, _
            .EncryptionTCN = trans.TCN, _
            .EncryptionORI = trans.EncryptionORI _
        }).ToList()

' Last X days
Dim listTrans As List(Of TransactionData) = _
    (From trans In db.TransHeaderTables _
     Where trans.CompletedDate >= Now.AddDays(x * -1)
    Select New TransactionData With _
        { _
            .TransId = trans.TransId.ToString, _
            .EncryptionTCN = trans.TCN, _
            .EncryptionORI = trans.EncryptionORI _
        }).ToList()

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33728491
Something like this
' --- Request 1 ---

Dim x as DateTime = Date.Parse("2010/9/9")

Dim y as DateTime = Date.Parse("2010/8/8")



Dim listTrans As List(Of TransactionData) = _

    (From trans In db.TransHeaderTables _

     Where trans.CompletedDate >= y  And trans.CompletedDate <= x

     Select New TransactionData With _

     { _

            .TransId = trans.TransId.ToString, _

            .EncryptionTCN = trans.TCN, _

            .EncryptionORI = trans.EncryptionORI _

     }).ToList()



' --- Request 2 ---     

Dim NumberOdDays As Integer = -5     

Dim z1 As DateTime = DateTime.Today

Dim z2 As DateTime = DateTime.Today.AddDays(NumberOdDays)     

     

Dim listTrans As List(Of TransactionData) = _

    (From trans In db.TransHeaderTables _

     Where trans.CompletedDate >= z2  And trans.CompletedDate <= z1

     Select New TransactionData With _

     { _

            .TransId = trans.TransId.ToString, _

            .EncryptionTCN = trans.TCN, _

            .EncryptionORI = trans.EncryptionORI _

     }).ToList()

     

Open in new window

0
 
LVL 1

Author Closing Comment

by:RichardRiga
ID: 33745436
Perfect.  Thanks so much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

13 Experts available now in Live!

Get 1:1 Help Now