Compare datetime

tiffany

Well-known member
Joined
Aug 7, 2005
Messages
57
Programming Experience
1-3
Hi, How can i compare the date and time? I want to compare the two date and time to see which is the easliest. Initially i need to reteieve the date and time from two tables and compare between the two. But how can i do so?


Thanks!
tiffany
 
Last edited:

tiffany

Well-known member
Joined
Aug 7, 2005
Messages
57
Programming Experience
1-3
compare the date and time

Hi. I had figure out the comparaion. But i can't convert the date and time to a string. How can this possible to be done? I have this following codes below:

Dim d As String
Dim t As String
t = DateTime.Now.ToString()

'While selectDateTime() is to retrieve the date and time from the database.
d = selectDateTime()
Dim t3 As New DateTime(100)
Dim t4 As New DateTime(20)

t3 = d
t4 = d

cn.Open()
If DateTime.Compare(t3, t4) > 0 Then
Dim query As String = "INSERT INTO Table1(msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "reply" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds


ElseIf DateTime.Compare(t3, t4) < 0 Then
Dim query As String = "INSERT INTO Table1 (msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "first" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds
End If
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,557
Location
Norway
Programming Experience
10+

Don Delegate

Well-known member
Joined
Jan 4, 2006
Messages
140
Location
Belgium
Programming Experience
10+
Use parameters?
Very handy for Date and DateTime and strings (with single quotes in them).

VB.NET:
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] msg [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Koekoek"
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand( _
[/SIZE][SIZE=2][COLOR=#800000]   "INSERT INTO Table1(msg, datTim,userID, status) VALUES (@msg, @datTim, @userID, @status)"[/COLOR][/SIZE][SIZE=2])
command.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#800000]"msg"[/COLOR][/SIZE][SIZE=2], msg)
command.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#800000]"datTim"[/COLOR][/SIZE][SIZE=2], DateTime.Now)[/SIZE]
[SIZE=2][SIZE=2][COLOR=#008000]' and so on ...
[/COLOR][/SIZE][/SIZE]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Don Delegate's advice is sound. You should NEVER use string concatenation to create an SQL statement like that. Having said that, it is only string literals that are enclosed in single quotes. A date literal in an SQL statement should be enclosed in "#" symbols.
 

tiffany

Well-known member
Joined
Aug 7, 2005
Messages
57
Programming Experience
1-3
Hi, thanks for the replies. I had tried convert datetiem into string.

Dim d as string
Dim t As String
t = DateTime.Now.ToString()

'selectDateTime() is to ret the date and time from ms sql
d = selectDateTime()
Dim t3 As New DateTime(100)
Dim t4 As New DateTime(20)

d = t3.ToString()
d = t4.ToString()

cn.Open()
If DateTime.Compare(t3, t4) > 0 Then
Dim query As String = "INSERT INTO msg(msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "reply" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds

ElseIf DateTime.Compare(t3, t4) < 0 Then
Dim query As String = "INSERT INTO msg(msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "first" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds
End If


But they nv did a comparaion. I need to get the earliest datetime and insert into the db with the status reply. If the system had determines that the second entry datetime is later then the previous one, it iwll insert into the db with the status first.

thank.
tiffany
 

Don Delegate

Well-known member
Joined
Jan 4, 2006
Messages
140
Location
Belgium
Programming Experience
10+
To compare DateTime, use the >, < and = operators.
VB.NET:
[COLOR=black][FONT=Courier New][COLOR=blue]Dim[/COLOR] d1 [COLOR=blue]As[/COLOR] DateTime = DateTime.Now
[COLOR=blue]Dim[/COLOR] d2 [COLOR=blue]As[/COLOR] DateTime = d1.AddMinutes(1.5)
[COLOR=blue]If[/COLOR] d1 > d2 [COLOR=blue]Then[/COLOR]
    MessageBox.Show([COLOR=blue]String[/COLOR].Format([COLOR=maroon]"{0} > {1}"[/COLOR], d1, d2))
[COLOR=blue]ElseIf[/COLOR] d2 > d1 [COLOR=blue]Then[/COLOR]
    MessageBox.Show([COLOR=blue]String[/COLOR].Format([COLOR=maroon]"{0} > {1}"[/COLOR], d2, d1))
[COLOR=blue]Else[/COLOR]
    MessageBox.Show([COLOR=blue]String[/COLOR].Format([COLOR=maroon]"{0} = {1}"[/COLOR], d1, d2))
[COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[/FONT][/COLOR]
 

tiffany

Well-known member
Joined
Aug 7, 2005
Messages
57
Programming Experience
1-3
Hi, i had trired your method. But i think the logic of my codes have something incorrect. Could anyone help me with it? I will be using controller and a presentation layer.

'This is the method where i get from the table and select the datetime to compare with another datetime. This codes will be place in the controller.
Public Function selectDateTime(ByVal subCat1 As String)
Dim a As String
Dim cmd As New SqlCommand
cn.Open()
cmd.CommandText = "SELECT datTim FROM msg WHERE subCat = '" + subCat1 + "'"
cmd.Connection = cn
a = cmd.ExecuteScalar()
cn.Close()
End Function

'This is the code where i place it in the presentation layer. Where n is equal to string and dl is to link with the controller class. strSubcat is the label.text
n = dl.selectDateTime(strSubcat)


'this is the method to insert into the db while select the time from db and compare with the now datetime.
Public Function insertNewMessage(ByVal strmsg As String, ByVal datTim As DateTime, ByVal userID As String, ByVal status As String) As DataSet

Dim d As String

Dim subCat1 As String

Dim t As DateTime = DateTime.Now
Dim t4 As DateTime = selectDateTime(subCat1)


cn.Open()
If DateTime.Compare(t, t4) Then
Dim query As String = "INSERT INTO msg(msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "first" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds

ElseIf DateTime.Compare(t, t4) > 0 Then
Dim query As String = "INSERT INTO msg(msg, datTim,userID, status) VALUES ('" & strmsg & "', '" & t & "','" & "s693" & "','" & "reply" & "')"
Dim myAdapter As New SqlDataAdapter(query, cn)
myAdapter.Fill(ds, "h")
cn.Close()
Return ds

End If
End Function


It able to insert teh now datetime and the message into the table. But it can't compare the datetime and insert the status to first or reply. Can anybody help me with this? Thankx.
Tiffany
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,557
Location
Norway
Programming Experience
10+
selectDateTime function doesn't return anything, also the return should be specified, if you want it to return string do:
VB.NET:
PublicFunction selectDateTime(ByVal subCat1 AsString) [B]As String[/B]
'...your code
[B]return a[/B]
EndFunction
(you also did n=dl.selectDateTime(strSubcat) where n is string)
Then in insertNewMessage function you have to do:
VB.NET:
Dim t4 As DateTime = Date.Parse(selectDateTime(subCat1))
You could also let the selectDateTime return a Date, and for instance use .ToShortDateString for your presentation layer.
 
Top Bottom