none
Between date's to next month not working correct RRS feed

  • Question

  • Hello,

    I have a problem with getting data between 2 date's when going to the next month.

    I store some data to a database , start date is date now and add 2 days to the end date.

    When the 2 days are over you can't get the data because it is looking between the 2 date's.

    That was working fine but now it is going to the next month feb and now i have a problem.

    I used it for a few weeks and never to the next month

               Startdate.Text = Date.Today.ToString("dd-MM-yyyy")
                Dim currentDate As DateTime
                ' Get the current date from the textbox
                currentDate = Startdate.Text
    
                ' Add two days
                currentDate = currentDate.AddDays(2)
                ' Write the date back to the textbox
                Enddate.Text = currentDate.ToString("dd-MM-yyyy") 
    Maybe it is counting wrong when going to the next month , now month 01 en going to  month 02 ????

    Thursday, January 31, 2019 9:30 AM

Answers

  • Found the problem

    I made new Columns Startdate and Expirationdate with datetime and not nvarchar !!!!

    Mist that , shame on me

    And used Parameters for Textbox Datuminvoer , what is the date of today

    cmd = New SqlCeCommand("Select Id, Papieren FROM Gegevens WHERE @IDate BETWEEN Startdate And Expirationdate ", con)
            cmd.Parameters.AddWithValue("@IDate", Datuminvoer.Text)

    Now it working correct , but now i have to alter all the exiting databases

    Thanks for the reply's Karen en Viorel_

    • Edited by KeesBlunder Sunday, February 3, 2019 5:44 AM
    • Marked as answer by KeesBlunder Sunday, February 3, 2019 5:44 AM
    Sunday, February 3, 2019 5:44 AM

All replies

  • Ignoring the formatting, this returns 2/2 for end date.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Startdate.Text = Date.Today.ToString()
            Dim currentDate As DateTime
            ' Get the current date from the textbox
            currentDate = CDate(Startdate.Text)
    
            ' Add two days
            currentDate = currentDate.AddDays(2)
            ' Write the date back to the textbox
            Enddate.Text = currentDate.ToString()
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 31, 2019 12:25 PM
    Moderator
  • Karen,

    I changed it but no luck.

    Using the european time .

    This is what it looks like now with your code , same outcome as before

    Thursday, January 31, 2019 1:41 PM
  • If I add two days I get 2-2-2019, what did you expect?


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 31, 2019 2:51 PM
    Moderator
  • Karen,

    It is  what i expect

    Can't find the problem why this is not working from one month to another.

    cmd = New SqlCeCommand("Select Id, Papieren FROM Gegevens WHERE Enddate BETWEEN '" & Startdate.Text & "' And '" & Expirationdate.Text & "' ORDER BY Id DESC ", con)

    A few days ago this is working fine , but now 2 days ( that i ad ) are going to februari it is not working any more.

    I change the date computer date to 1 feb and it is working again , but not on 31-01-2019 ????

    Maybe it is counting wrong ? i don't know

    For example
    
    startdate 31-01-2019 and enddate 02-02-2019 '' not working
    
    startdate 01-02-2019 and enddate 03-02-2019 '' working ?????

    In the database are the date's the same as in the example

    So the search in the database is going wrong when passing the month i think

    Thursday, January 31, 2019 6:43 PM
  • Does it work in this case?

    cmd = New SqlCeCommand("Select Id, Papieren FROM Gegevens WHERE Enddate BETWEEN '" & CDate(Startdate.Text).ToString("yyyy'-'MM'-'dd") & "' And '" & CDate(Expirationdate.Text) .ToString("yyyy'-'MM'-'dd") & "' ORDER BY Id DESC ", con)

     

    To avoid problems, you should use “Parameterised Queries”.

     

    Thursday, January 31, 2019 7:14 PM
  • That is odd, it should be the same as this.

    SELECT id,
           StartDate
    FROM dbo.DateTable_1
    WHERE dbo.DateTable_1.StartDate BETWEEN GETDATE() AND GETDATE() + 2
    ORDER BY id


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 31, 2019 7:26 PM
    Moderator
  • Viorel_

    Same result , i think the problem is that it is only looking at the 'dd' and not when it is going to the next month

    BETWEEN '" & Startdate.Text & "' And '" & Expirationdate.Text & "'

    Today is the first of feb , now it is working again . Shows the data between Startdate and Expirationdate.

    Between 01-02-2019 and 03-02-2019
    Is there an other way to look between 2 date's ?


    Friday, February 1, 2019 3:43 AM
  • Found the problem

    I made new Columns Startdate and Expirationdate with datetime and not nvarchar !!!!

    Mist that , shame on me

    And used Parameters for Textbox Datuminvoer , what is the date of today

    cmd = New SqlCeCommand("Select Id, Papieren FROM Gegevens WHERE @IDate BETWEEN Startdate And Expirationdate ", con)
            cmd.Parameters.AddWithValue("@IDate", Datuminvoer.Text)

    Now it working correct , but now i have to alter all the exiting databases

    Thanks for the reply's Karen en Viorel_

    • Edited by KeesBlunder Sunday, February 3, 2019 5:44 AM
    • Marked as answer by KeesBlunder Sunday, February 3, 2019 5:44 AM
    Sunday, February 3, 2019 5:44 AM
  • Glad you have resolved this, I would had not guessed you didn't use a date type.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, February 3, 2019 10:16 AM
    Moderator