11 Replies - 10759 Views - Last Post: 18 June 2012 - 11:41 PM

#1 Kindeboeno   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 19-December 10

Comparing current date against Database date

Posted 29 May 2012 - 10:02 AM

Hi guys, I have a MYSQL database with a table Called events and in it, there is a column called UploadDate. Currently I'm stuck at figuring out how to formulate a select statement that compares the current date and the UploadDate.

If the difference is 1 week, it will display the items that have been uploaded in that 1 week.

Can anyone help on this?
Is This A Good Question/Topic? 0
  • +

Replies To: Comparing current date against Database date

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 616
  • View blog
  • Posts: 4,090
  • Joined: 12-January 10

Re: Comparing current date against Database date

Posted 29 May 2012 - 10:07 AM

use the getdate() to get the current date

use <> to say not equal too

and you can add an amount of days such as 7 for the range Ill help more after lunch
Was This Post Helpful? 0
  • +
  • -

#3 Kindeboeno   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 19-December 10

Re: Comparing current date against Database date

Posted 29 May 2012 - 10:16 AM

View PostDarenR, on 29 May 2012 - 10:07 AM, said:

use the getdate() to get the current date

use <> to say not equal too

and you can add an amount of days such as 7 for the range Ill help more after lunch


I'm currently using VB.net to do my project. So I'll first store the current date in a variable and then compare that variable against the uploadDate column? I forgot to add that the data type for uploadDate is datetime, however I just need to compare the days.

Thanks for the help!
Was This Post Helpful? 0
  • +
  • -

#4 Bresh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 22-May 12

Re: Comparing current date against Database date

Posted 14 June 2012 - 12:00 AM

View PostKindeboeno, on 29 May 2012 - 10:02 AM, said:

Hi guys, I have a MYSQL database with a table Called events and in it, there is a column called UploadDate. Currently I'm stuck at figuring out how to formulate a select statement that compares the current date and the UploadDate.

If the difference is 1 week, it will display the items that have been uploaded in that 1 week.

Can anyone help on this?

Here is a piece of my code, worked for me... you can try it out
   Dim date1 As Date
    Dim expdate As Date
    Dim difference As TimeSpan
    Dim cmd As MySqlCommand
    Dim myreader As MySqlDataReader
    Dim myconnection As MySqlConnection

        myconnection = New MySqlConnection("Server=localhost;username=root;password=;database=db; allow zero datetime=no")
        myconnection.Open()
       
        date1 = Convert.ToDateTime(Today)

        cmd = New MySqlCommand("select expiry_date from purchases", myconnection)


        myreader = cmd.ExecuteReader()

        While myreader.Read()

            expdate = CType(myreader.GetMySqlDateTime("expiry_date"), Date)
            difference = expdate.Subtract(date1)

Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Comparing current date against Database date

Posted 14 June 2012 - 02:45 PM

Use a clause like this:
WHERE DATEDIFF(NOW(), Events.UploadDate) BETWEEN 0 AND 7


...or if you need the granularity to be better than days...
WHERE (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(Events.UploadDate)) BETWEEN 0 AND (60*60*24*7)


Was This Post Helpful? 1
  • +
  • -

#6 Bresh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 22-May 12

Re: Comparing current date against Database date

Posted 15 June 2012 - 12:11 AM

View Poste_i_pi, on 14 June 2012 - 02:45 PM, said:

Use a clause like this:
WHERE DATEDIFF(NOW(), Events.UploadDate) BETWEEN 0 AND 7


...or if you need the granularity to be better than days...
WHERE (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(Events.UploadDate)) BETWEEN 0 AND (60*60*24*7)



Where and how do i use this code? It looks like exactly what i need. Am using vb.net/mysql
Was This Post Helpful? 0
  • +
  • -

#7 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Comparing current date against Database date

Posted 15 June 2012 - 03:04 AM

You use it as a clause in your SQL query string, the format should be something like this:
SELECT *
FROM MyTable
WHERE  --Clause text goes here


Was This Post Helpful? 0
  • +
  • -

#8 Bresh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 22-May 12

Re: Comparing current date against Database date

Posted 18 June 2012 - 03:51 AM

I have tried this...
 myconnection = New MySqlConnection("Server=localhost;username=root;password=;database=agro-system; allow zero datetime=no")
        myconnection.Open()
       
        date1 = Convert.ToDateTime(Today)

        cmd = New MySqlCommand("select expiry_date from purchases", myconnection)


        myreader = cmd.ExecuteReader()

        While myreader.Read()

            expdate = CType(myreader.GetMySqlDateTime("expiry_date"), Date)
            difference = expdate.Subtract(date1)


            ar()

          

        End While
        myreader.Close()
        myconnection.Close()
    End Sub
   
    Sub ar()
        With myreader
            If .Read Then
                cmd1 = New MySqlCommand("Select name from purchases Where difference(Now(), Events.expdate) BETWEEN 0 AND 7", myconnection)
                myreader = cmd1.ExecuteReader()
                While myreader.Read()
                    Console.WriteLine("The following Items Shall expire in less than 7 Days" + (myreader.GetString(0)))
                End While

            End If
        End With
    End Sub
But am getting this error

Quote

There is already a data reader associated with this connection which must be closed first

Was This Post Helpful? 0
  • +
  • -

#9 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Comparing current date against Database date

Posted 18 June 2012 - 03:55 AM

Not sure, you'll need to ask in the C# forums to solve that problem (I'm assuming you're using C#)
Was This Post Helpful? 0
  • +
  • -

#10 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Comparing current date against Database date

Posted 18 June 2012 - 04:03 AM

I'm guessing the problem there is that you are trying to execute a query using myconnection while you are still reading data from a previous query execution on that connection. You have to execute one query and read all the data, and then execute the other query once that is done. Either that or open multiple connections.

Also, one thing makes no sense. You are executing the ar() sub within a loop that goes through each row in the myreader result set. However in the ar() sub you are trying to execute another query and assigning it's result set to myreader. - Logically, if the query were actually executing rather than producing the error you are getting, on the very first iteration of your original loop, the ar() function would override the myreader set and it would never show anything beyond the first row of the original query.

My point is, you are reusing a variable used to control a loop inside the loop, thus messing up the loop itself. Kind of like:
myNumber = 0
While myNumber < 10
    System.Write(myNumber)
    muNumber = 0
    While myNumber < 20
        System.Write(" - {0}", myNumber);
        muNumber += 1
    End While
    System.WriteLine()
    myNumber += 1
End While


This will always end on the first iteration of the first loop, rather than count to 10.

@e_i_pi It's VB.NET, not C# ;)

This post has been edited by Atli: 18 June 2012 - 04:05 AM

Was This Post Helpful? 1
  • +
  • -

#11 Bresh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 22-May 12

Re: Comparing current date against Database date

Posted 18 June 2012 - 11:27 PM

Actually, its my first time using mysql and am kind of new to programming... thanks all for the help :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#12 Bresh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 22-May 12

Re: Comparing current date against Database date

Posted 18 June 2012 - 11:41 PM

View PostBresh, on 18 June 2012 - 11:27 PM, said:

Actually, its my first time using mysql and am kind of new to programming... thanks all for the help :bigsmile:

also new to vb.net
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1