Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

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

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
RichardRiga
Asked:
RichardRiga
1 Solution
 
käµfm³d 👽Commented:

' 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
 
Fernando SotoRetiredCommented:
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
 
RichardRigaAuthor Commented:
Perfect.  Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now