Date between 2 collumn

[ann.arceo.14]

gud day.
I have 2 collumn from my database. DateHired ( datarow.item(5)) and EndofContract (datarow.item(6))
on my form i have 2 datetimepicker to record the employees previous jobs, i need to avoid overlapping on the employees recent recorded jobs.
e.g if date hired is 01/02/2012 and end of contract is 05/30/2012 all dates beetween it must not be available if the user try to input date between it, it must notify the user that it is invalid.
my code dont have errors yet it doesnt function well.
e.g if ihave saved date hired is 01/02/2012 and end of contract is 05/30/2012, if again i try to input 01/05/2012 to 05/30/2012 it can trace that its not available yet if i input 01/01/2012 to 05/30/2012 it just didnt trace it,, i dont know how to implement this right hope someone to help.. thanks

 Private Function datetest()
        Dim con As New OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =..\systemsdbs.accdb"
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        con.Open()
        Dim da As New OleDbDataAdapter("select * from tblempjobhist where ID like '%" & lbljobhistid.Text & "%'", con)
        da.Fill(dt)


        For Each datarow In dt.Rows


            If ((txtjobhired.Value.Date >= datarow.item(5)) And (txtjobend.Value.Date <= datarow.item(6))) Then
                con.Close()
                Return True
            End If

        Next
        con.Close()
        Return False

    End Function

if record exist it must return true, otherwise it will return false.

[M.Waqas Aslam]

"select * from table1 where @yourDate between hiringdate and contractEndDate and id = @ID

'@yourDate and @ID are the variable fields.

try this , may be this will solve your prob.

Regards

[G_Waddell]

Quick suggestion by default, if you place a date value with no time part midnight ie. 00:00:00 is assumed to be the time part. So if I'm doing a date compare like that, I add the time value "23:59:59" to my end date to ensure I catch any dates.

Also, different date formats can really cause problems e.g. is 02/05/2012 the second of May 2012 or the 5th of February 2012? - it depends what part of the world you are from.

I always format my dates (both from My code and from SQL) into a format that is unambiguous e.g. YYYY-MM-DD or DD-MMM-YYYY e.g.

dim sSQL as string
dim StartDate as String
dim EndDate as String


StartDate = format(InputDate1, "yyyy-mm-dd")
EndDate = format(InputDate2, "yyyy-mm-dd")  &" 23:59:59" 

sSQL ="SELECT CONVERT(nvarchar(30), MyDate, 106) AS MyDate From MyTable " & _
"WHERE (MyDate >= '" &StartDate & "') AND ( MyDate <='" &EndDate &"')"

Now your SQL server will know exactly what dates you mean and also it will return the date value in DD-MMM-YYYY format i.e. 02 May 2012, so your code will be able to understand it too.

[saphiro]

"select * from table1 where @yourDate between hiringdate and contractEndDate and id = @ID
'@yourDate and @ID are the variable fields.
try this , may be this will solve your prob.

Regards

thanks for the replies,, just wanna ask how can i assign value to the variables.
my database have 2 collumn, datehired and dateend so how can i assign its value to that @mydate?
i really dont know, is it something like cmd.parameters.addwithvalue ("@mydate", ???)
hope you wont mind.?

[Maligui]

If the type is of DateTime (this includes Date) just insert it like so.

Dim MyHireDate As Date
Dim MyEndDate As Date

cmd.Parameters.AddWithVlue("@MyHireDate", MyHireDate)
cmd.Parameters.AddWithVlue("@MyEndDate", MyHireDate)

[saphiro]

my database have 2 collumn, datehired and dateend so how can i assign its value to that @mydate?

how can i assiign the value between that two collumn on that @mydate?

[Maligui]

Dim CurrentDate as Date

cmd.Parameters.AddWithVlue("@MyDate", CurrentDate)

Should assign both mydates in the SQL statement.

[saphiro]

im kinda lost can you just modify the code above,, the first part.. can you?

[Maligui]

Now that I took a good look over your code, you are trying to validate on the client side. You have the one date columns DateHired and EndOfContract. You want to void the dates that are before the DateHired field and after the EndOfContract field, right?

[saphiro]

yes but that is two date collumn.. and i want to void inputs <= datehired and >= endofcontract..
can you help me out?

[saphiro]

users must be prohibited saving date which fall between those dates to be save again on the database

[Maligui]

Tested and works:

Private Function datetest() As Boolean
    Dim con As New OleDbConnection
    Dim dt As New DataTable
    Dim ds As New DataSet
    Dim da As OleDbDataAdapter

    ' the variable will be true is the dates are valid.
    Dim IsValid As Boolean = False

    Try

        ' setup dataset
        ds.Tables.Add(dt)
        ' assign the connection string
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =..\systemsdbs.accdb"
        ' assign data adapter
        da = New OleDbDataAdapter("select * from tblempjobhist where ID like '%" & lbljobhistid.Text & "%'", con)
        ' open the connection
        con.Open()
        ' fill the dataset
        da.Fill(dt)

        For Each datarow As DataRow In dt.Rows
            ' we'll use variables to handle the data.
            ' convert them to date.
            Dim HireDate As Date = CType(datarow.Item(5), Date)
            Dim EndOfJobDate As Date = CType(datarow.Item(6), Date)

           ' If date is between HireDate and EndOfJobDate the validation will fail.
            If txtjobhired.Value.Date <= HireDate And txtjobend.Value.Date >= EndOfJobDate Then
                'This is not needed since you are closing the connection after the loop.
                'con.Close()
                IsValid = True
            End If
        Next

    Catch ex As Exception
        MsgBox("there was an error with the data" & ex.Message)
    Finally
        ' always close the connection.
        con.Close()
    End Try

    ' return the result.
    Return IsValid
End Function

[saphiro]

thanks its working however theres a bit problem.
since the code used AND condition, if record have 7/16/2012 to 8/16/2012 all dates between is prohibited yet if i try to input 7/15/2012 to 8/16/2012, it doesnt function the way it must be since it will return false right?
thank you for the effort you have given, it is really appreciiated.

[Maligui]

Right, all dates between HireDate and EndOfJob date will return False, meaning the two dates given are invalid. From what I gathered you wanted the dates between the given dates to be invalid. If you want the dates between the given dates to be valid, switch the <= and the >= operatives.

[saphiro]

no actually you did it right,, the things is if the database have a record of
7/16/2012 to 8/16/2012
and i try to save
7/14/2012 to 8/16/2012
this will be accepted, but it shouldnt be.
thanks
just wanna ask if the value between or dates between DeateHired and EndOfJobDate can be put into a single variable to be used in comparison?

[Maligui]

That's correct.

[saphiro]

what do you mean?

[saphiro]

ill try to explain it further,
my database have a record of 7/16/2012 to 8/16/2012
so any dates in between should not be accepted,

but when i try to save date like 7/14/2012 to 8/16/2012
it save the record, but it should not cause its not valid.

so what should i do?

[Maligui]

the funciton datetest returns a boolean value. This is where you check the dates.

For example.

Private Sub SaveData()
    Dim IsDatesValid As Boolean

    ' Check that the dates are valid
    IsDatesValid = datetest

    If IsDateValid Then
        ' the fuction returned true. The date range is valid.
        ' Save Data
    End If
End Sub

[saphiro]

sir its not that i mean with regards the input,, if database have records
like datehired : 7/16/2012 and EndofJobDate: 8/16/2012

and i try to save another data which is like datehired : 7/14/2012 and EndofJobDate: 8/16/2012
then it reads it as a valid one but it is not because the the EndofJobDate is the only date inside the range and datehired is not valid.. just try to see the date inputs, how they differ from each other,
again record is datehired : 7/16/2012 and EndofJobDate: 8/16/2012
new date to save datehired : 7/14/2012 and EndofJobDate: 8/16/2012

[Maligui]

This is the code I used to test. I have entered arguments for the funciton datetest, commented out the fill of the dataset, and set the data manually. The data is valid (true) if the dates fall within range, invalid (false) if they do not.

Private Sub TestData()
        Dim IsValid As Boolean

        ' return true since both dates are in the range of
        ' 7/16/12 - 8/16/12
        IsValid = datetest(#7/16/2012#, #8/16/2012#)
        MsgBox(IsValid)

        ' returns false because 7/14/12 is before the hire date (startDate).
        IsValid = datetest(#7/14/2012#, #8/16/2012#)
        MsgBox(IsValid)
    End Sub

    Private Function datetest(startDate As Date, endDate As Date) As Boolean
        ' uncomment these lines for production 

        Dim con As New OleDbConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)

        'con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =..\systemsdbs.accdb"
        'con.Open()

        'Dim da As New OleDbDataAdapter("select * from tblempjobhist where ID like '%" & lbljobhistid.Text & "%'", con)
        'da.Fill(dt)

        ' we will make a default dataset to debug data. The rows with dates will be filled
        ' with conditional data. Notice the dates I am using 
        dt.Columns.Add("0")
        dt.Columns.Add("1")
        dt.Columns.Add("2")
        dt.Columns.Add("3")
        dt.Columns.Add("4")
        dt.Columns.Add("5")
        dt.Columns.Add("6")
        dt.Rows.Add(0, 1, 2, 3, 4, #7/16/2012#, #8/16/2012#)

        ' the variable will be true is the dates are valid.
        Dim IsValid As Boolean = False

        For Each datarow As DataRow In dt.Rows
            ' we'll use variables to handle the data.
            ' convert them to date.
            Dim HireDate As Date = CType(datarow.Item(5), Date)
            Dim EndOfJobDate As Date = CType(datarow.Item(6), Date)

            If startDate >= HireDate And endDate <= EndOfJobDate Then
                'This is not needed since you are closing the connection after the loop.
                'con.Close()
                IsValid = True
            End If
        Next
        con.Close()
        Return IsValid

    End Function

[M.Waqas Aslam]

saphiro did you tried this query

"select * from table1 where @yourDate between hiringdate and contractEndDate and id = @ID

'@yourDate and @ID are the variable fields.

[saphiro]

M.Waqas Aslam

i havnt sir because @yourdate is a single variable and i dont know how to properly assign its value and also i am refering to 2 value.
i mean i have 2 date collumn from my database and 2 datetimepicker on my form..

i just want to prohibit user from entering dates that is in between of those dates saved on my database wirh the same employee.
im dealing with the records of their previous jobs,

if employee1 have a previous job having datehired= 07/17/2012 and dateend= 08/17/2012
then if again i try to add another previous job for employee1 that have dates which is in between that dates above, error message must be prompted, telling that it is invalid,
because any dates in between these dates --> (datehired= 07/17/2012 and dateend= 08/17/2012) are not allowed..

i was able to run the code of Sir Maligui, it does really prohibit to enter dates in between, unfortunately if i try to save dates like --> (datehired= 07/14/2012 and dateend= 08/17/2012) for employee1 it allows it.. so it mess up right .

so what am i supposed to do?
pardon for im just a newbie,

[Maligui]

You will need to re write the code. The code I provided was to prohibit dates within that range to be selected. To allow dates to be accepted within the range you need to swap the <= and >= operatives

' Allows dates OUTSIDE HireDate and EndDate range
If startDate >= HireDate And endDate <= EndOfJobDate Then
    IsValid = True
End If

' Alllows dates that are inside the HireDate and EndOfJob rage
If startDate <= HireDate And endDate >= EndOfJobDate Then
    IsValid = True
End If

Of course, you can use the one i provided earlier code for both operations.

Rename the datetest to

Private Function OutSideRange(startDate As Date, EndDate as Date) As Boolean

Then when you test data, if it is outside the range it returns true; Otherwise false.

[saphiro]

thank you sir thank you very much it is ok now..
thank you for sharing your knowledge.
i implement your codes, i used 2 datetest..