Hi Can anyone please check my code ?
I'm trying to get today's date from database
I tried many ways and I'm stuck now..

at my database access
the transdate appears like this

11/29/2009 2:02:23 AM

and this is my query code from VB.NET

"SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                             "Where UserID =" & AccID & " and TransDate = " & DateTime.Today() & " " & _
                             "ORDER BY TransID"

is there anything wrong ? I debug and I got the date correct but the data won't show up
please help..

8 Years
Discussion Span
Last Post by Dorayaki

Database's hold a DateTime value so its important to include the time such as using a between Todays date at 12:00am and tomorrows date


uhm sorry i don't get it..

anyway i had tried using # for the date, but still i can't get the value

i only want to get today's date not including the time
is it possible ?


Time will automatically be applied as 12am thats why its better to use a between statement to give you all records for the specified day.


Where do you want the data to appear?

you have selected the data, but you havnt said what you want to do with it?


I want to show it on the listview

Here is my full code

Sub loadtranstoday()
            Dim current As DateTime = DateSerial(Now.Year, Now.Month, Now.Day)
            Dim sstatement = "SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                             "Where UserID = " & AccID & " and TransDate = #" & current & "# " & _
                             "ORDER BY TransID"
            Dim sCommand As OleDbCommand = New OleDbCommand(sstatement, sConnection)
            Dim sReader As OleDbDataReader = sCommand.ExecuteReader()

            While sReader.Read()
                lstViewDWToday.Items.Add(Format(sReader(0), "dd/MM/yyyy"))
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(1).ToString)
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(2).ToString)
                lstViewDWToday.Items(lstViewDWToday.Items.Count - 1).SubItems.Add(sReader(3).ToString)
            End While
        Catch sException As System.Exception
            MsgBox(sException.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Critical)
        End Try
        If sConnection.State <> ConnectionState.Closed Then sConnection.Close()
        If lstViewDWToday.Items.Count > 0 Then lstViewDWToday.Items(0).Selected = True
    End Sub

When I set breakpoint, it run until While sReader.Read() and then Jump to End while , it skip the whole thing inside..
but If I delete the "transdate = current" then it works
but what I want is to show the current date...
so any idea ?


Dim current As Date = Date.Now
        Dim sstatement = "SELECT TransDate, Deposit, Withdrawal, TotalAmount FROM Transactions " & _
                            "Where UserID = " & AccID & " and TransDate LIKE '" & current.ToShortDateString & "' " & _
                            "ORDER BY TransID"

current = 11/30/2009 7:32:14 AM
current.ToShortDateString= 11/30/2009

so you search in your query for every entry thats like 11/30/2009 which should solve the problem.

the # that one of the posters mentioned is only visible in debugging to mark it as date, but never shows up anywhere.


Not working, anyway I found out a way to do it..

you might want to know how to do it..
so here we go..

"Where UserID = " & AccID & " and Day(TransDate) = " & Now.Day & " and Month(TransDate) = " & Now.Month & " and Year(TransDate) = " & Now.Year & " " & _
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.