Solved

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

Posted on 2010-09-21
3
636 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 75

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 63

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

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.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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