VB.Net 2008 - DateDiff Function

[dollipop]

Hi again

I just wanted to ask if it is possible to use the DateDiff function and insert its result into a table in the database?

And if so, what would be the code for it? ^___^

thank you again for your help~

[Hack]

Of course you can.

INSERT INTO sometable (field1) 
VALUES (DateDiff("d", Now(), DateSerial(Year(Now()), 12, 31)))

[Phil Weber]

What kind of database? Do you want to update a single row, as Hack showed, or several rows?

[dollipop]

Of course you can.

INSERT INTO sometable (field1) 
VALUES (DateDiff("d", Now(), DateSerial(Year(Now()), 12, 31)))

wow. thanks~
what if I have to base the time using two different fixed times?
like one would be 08:00:00 and the other would be 16:00:00.

[Hack]

Ok. So are you saying you would need to separate records created; one for 08:00:00 and the other for 16:00:00?

What are you doing exactly?

And you still haven't said what kind of database you are using. That can, and often does, make a difference in coding.

[dollipop]

What kind of database? Do you want to update a single row, as Hack showed, or several rows?

SQL
well., just a single row but having a code for several rows would be helpful as well.

your help is much appreciated thank you~

[dollipop]

No. Not really. More like, since they would both be stored in one table, I just need to know if the DateDiff function works in an If statement and a column in a table can be a condition like:

 

Dim a,b as DateTime
a = 08:00:00
b= 16:00:00

If TIME.Time_In >= a then
(datediff function)

else

condition;

I'm using SQL

I'm currently doing a payroll system as a final requirement. And the company has two types of shifts. Regular and graveyard.

[Hack]

Ok.

You are using SQL Server and writing your code in VB.NET, right?

DateDiff can be used in an IF statement.

I really don't know what to say to you.

Most of what you have said is in the form of a statement, rather than question. And, the questions arre really, really broad based and general.

Perhaps if you have a specific question, we could be more help.

[dollipop]

Oh. I'm sorry ^^

Ok. Uhm, I have two tables right now. one named attend and the other time. Time stores the Emp_ID, Time_In, Time_Out and Minutes_Late of the employee. But the since the company has two shifts, we decided to make a separate (which is attend) to store the fixed times where we will base the DateDiff. The problem now is, would it be possible to retrieve the fixed time from another table and use those values in a DateDiff function in the time table? ^^

[dollipop]

sorry bout that. here is the code I am using right now:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim timeout As String
        Dim user As String
        Dim tim As String

        con = New SqlConnection("Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\Users\Carlo\Desktop\OTTO Payroll System\OTTO Payroll System\OTTOdBase.mdf;User Instance=true")
        con.Open()

        user = "'" & Trim(TextBox1.Text) & "'"
        timeout = "'" & Trim(Label1.Text) & "'"
        Try
            If CType(Me.ACCESSTableAdapter.LoginQuery(Me.TextBox1.Text, _
                     Me.TextBox2.Text), Integer) > 0 Then

                tim = "UPDATE TIME SET Time_Out = " & timeout & ", Minutes_late = DateDiff(mi, ATTEND.TimeStart, TIME.Time_In) From TIME INNER JOIN ATTEND ON TIME.Emp_ID = ATTEND.Emp_ID WHERE Sessions = Sessions"
                com = New SqlCommand(tim, con)
                com.ExecuteNonQuery()
                MsgBox("Hachoo!")
            Else
                MsgBox("Invalid Username and/or Password!.")

            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

What confuses me is that when I log out, the last time for the log out is updated on all rows instead of just the row it's supposed to be in. like so:



I tried chaging the UPDATE statement but still, nothing happens. What can be my course of action for this? >_<

Your help is much appreciated..

[Hack]

What confuses me is that when I log out, the last time for the log out is updated on all rows instead of just the row it's supposed to be in.

This tells me your WHERE clause is not working properly.

tim = "UPDATE TIME SET Time_Out = " & timeout & ", 
Minutes_late = DateDiff(mi, ATTEND.TimeStart, TIME.Time_In) From TIME INNER JOIN ATTEND ON TIME.Emp_ID = ATTEND.Emp_ID 
WHERE Sessions = Sessions"

What does "WHERE sesssions = sessions" mean?