locked
Using the date from DateTimePicker in a windows form to select dates from a MySql table 1 month old in VB.NET RRS feed

  • Question

  • Hi, I am playing with dates in a windows form. I have  a DateTimePicker which shows the current day. I am filling a DataGrid with the returned values of a MySql database.

    I can now get the returned values of dates I select in my DateTimePicker which is great. For instance the code below will select all dates in a MySql database that are higher or equal to the date I select by clicking on the DateTimePicker. @StartDate ref to DateTimePicker

    Dim Query = "select id, UserName, Serial, TimeDate, Image1, Image2 FROM Users WHERE TimeDate >= @StartDate AND Company ='" + CompanyNameTxt.Text() + " '"

    Im not sure how to select dates that are 30 days or 1 month earlier than the date showing on the DateTimePicker. Can someone please give me an example.

    Regards

    Saturday, August 8, 2020 4:59 PM

Answers

  • Try calculating this value:

    Dim startDate As Date = DateTimePicker1.Value.Date.AddMonths(-1)

    Then pass it to MySQL via @StartDate parameter.

    (By the way, you can introduce another parameter for Company: ‘…WHERE TimeDate >= @StartDate AND Company = @Company’).

    • Marked as answer by nigelsvision Tuesday, August 11, 2020 8:55 PM
    Saturday, August 8, 2020 7:59 PM
  • Try this

    SELECT id, UserName, Serial, TimeDate, Image1, Image2 
    FROM   Users 
    WHERE  TimeDate > (@StartDate - INTERVAL 1 MONTH) AND ....;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by nigelsvision Sunday, August 9, 2020 7:21 AM
    Saturday, August 8, 2020 7:48 PM

All replies

  • Try this

    SELECT id, UserName, Serial, TimeDate, Image1, Image2 
    FROM   Users 
    WHERE  TimeDate > (@StartDate - INTERVAL 1 MONTH) AND ....;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by nigelsvision Sunday, August 9, 2020 7:21 AM
    Saturday, August 8, 2020 7:48 PM
  • Try calculating this value:

    Dim startDate As Date = DateTimePicker1.Value.Date.AddMonths(-1)

    Then pass it to MySQL via @StartDate parameter.

    (By the way, you can introduce another parameter for Company: ‘…WHERE TimeDate >= @StartDate AND Company = @Company’).

    • Marked as answer by nigelsvision Tuesday, August 11, 2020 8:55 PM
    Saturday, August 8, 2020 7:59 PM
  • Hi Karen, thanks for the reply and your code does answer my question so I have marked it as the answer.  Unfortunately it wasn't until I tried the code that I realised I have not actually asked the correct question.

    What I want to do is extract only those that are 1 month or 30 days old. where as the code you have provided me with will extract all dates up to 1 month.

    Thanks Nige

    Sunday, August 9, 2020 7:20 AM
  • Hi Viorel, thanks for the reply, I marked Karen's post as the answer on this one simply because her response came first. I just wanted to say thankyou.

    Nige

    Sunday, August 9, 2020 7:23 AM
  • Hi Viorel, thanks for the reply, I marked Karen's post as the answer on this one simply because her response came first. I just wanted to say thankyou.

    Nige

    Hello Nige,

    You can also mark Viorel_ as an answer too, nothing says that there can't be more than one answer :-)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, August 9, 2020 11:14 AM
  • I have done now Karen, I had no idea we could mark 2 answers as correct. I always thought it was just one. Thanks Nige
    Tuesday, August 11, 2020 8:58 PM