CODEWITHSUNDEEP
sql-servervb.net
Sattar5
Sattar5

Reputation: 17

Select last 24 hours data from SQL Server in VB.NET

I have two columns in my table, Date and Total_Amount. I need to display the data from the last 24 hours, and sum the result and show that sum in a textbox using VB.NET.

Dim cmd As New SqlCommand
cmd.Connection = cn
cmd.CommandText = "Select Sum(Total_Amount) as Total_AmountSum from Table_10"

Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)

If table.Rows.Count() > 0 Then
    TextBox1.Text = table.Rows(0)("Total_AmountSum").ToString()
End If

Upvotes: 0

Views: 308

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You have to use DATEADD here

Try this

For last 24 hours

    Dim cmd As New SqlCommand
    cmd.Connection = cn
    cmd.CommandText = "Select Sum(CASE WHEN [Date] BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE() THEN Total_Amount END) As Total_AmountSum from Table_10"
    Dim adapter As New SqlDataAdapter(cmd)
    Dim table As New DataTable()
    adapter.Fill(table)
    If table.Rows.Count() > 0 Then
        TextBox1.Text = table.Rows(0)("Total_AmountSum").ToString()
    End If

For last 1 month

    Dim cmd As New SqlCommand
    cmd.Connection = cn
    cmd.CommandText = "Select Sum(CASE WHEN [Date] BETWEEN DATEADD(mm, -1, GETDATE()) AND GETDATE() THEN Total_Amount END) As Total_AmountSum from Table_10"
    Dim adapter As New SqlDataAdapter(cmd)
    Dim table As New DataTable()
    adapter.Fill(table)
    If table.Rows.Count() > 0 Then
        TextBox1.Text = table.Rows(0)("Total_AmountSum").ToString()
    End If

For last 1 week

Dim cmd As New SqlCommand
cmd.Connection = cn
cmd.CommandText = "Select Sum(CASE WHEN [Date] BETWEEN DATEADD(ww, -1, GETDATE()) AND GETDATE() THEN Total_Amount END) As Total_AmountSum from Table_10"
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable()
adapter.Fill(table)
If table.Rows.Count() > 0 Then
    TextBox1.Text = table.Rows(0)("Total_AmountSum").ToString()
End If

Upvotes: 1

Divyesh patel
Divyesh patel

Reputation: 987

You may have to use below query:

Select Sum(Total_Amount) as Total_AmountSum from Table_10
WHERE   [Date]>= DATEADD(day, -1, GETDATE())

Upvotes: 0

Related Questions