Compare datetime

[tiffany]

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

 

[tiffany]

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]

have you checked with documentation? perhaps one of the .to(short/long)(date/time)string methods suits?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDateTimeMethodsTopic.asp

 

[Don Delegate]

Use parameters?
Very handy for Date and DateTime and strings (with single quotes in them).

[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]

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]

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]

To compare DateTime, use the >, < and = operators.

[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]

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]

selectDateTime function doesn't return anything, also the return should be specified, if you want it to return string do:

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:

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.