VB.NET string to datetime

I am wrestling with some code to convert string to datetime in VB.NET, and then insert it into an MSSQL database.

I am using MSSQL, and the format of the datetime field in the MSSQL database is:  m/d/yyyy h:mm:ss tt  (where tt is AM/PM).

I am combining a string for this from:

a) retrieval from another database table, for the date part
   --> MyDate = "SELECT convert(varchar,gamedatetime,101) "

b) Adding in the time from input from textboxes
   --> MyTime = Scoretime3.Text & ":" & Scoretime4.Text & ":" & Scoretime5.Text & " PM"

On screen (using a label) I look at the date and it looks fine. But I am having trouble converting it.

I have tried MyDateTime = DateTime.Parse(MyDate & " " & MyTime)

and also tried the Convert and Format function to not avail - always getting an error that string was in the wrong format.

Can someone help me with the correct way to convert my string so it can be inserted in the database ?

Thanks, Peter

pbisseggerAsked:
Who is Participating?
 
whityumCommented:
CType(MyDate & " " & MyTime, DateTime)
0
 
Roopesh_7Commented:
hey no way for error... check the value of  Mydate, Mytime...

its right formate...i mean values are current if it is curret check the culture you want is 12 hour or 24 hour?

below code will help you in all the three cases

Imports System
Imports System.Globalization

Class Class1
   Public Shared Sub Main()
      ' Assume the current culture is en-US.
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim myDateTimeValue As String = "2/16/1992 12:15:12"
      Dim myDateTime As DateTime = DateTime.Parse(myDateTimeValue)
      Console.WriteLine("1) myDateTime       = {0}", myDateTime)
     
      ' Reverse month and day to conform to a different culture.
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim culture As CultureInfo = New CultureInfo("fr-FR", True)
      Dim myDateTimeFrenchValue As String = "    16/02/1992 12:15:12"
      Dim myDateTimeFrench As DateTime = _
                           DateTime.Parse(myDateTimeFrenchValue, _
                                          culture, _
                                          DateTimeStyles.NoCurrentDateDefault)
      Console.WriteLine("2) myDateTimeFrench = {0}", myDateTimeFrench)
     
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim expectedFormats As String() =  {"G", "g", "f", "F"}
      myDateTimeFrench = DateTime.ParseExact(myDateTimeFrenchValue, _
                                          expectedFormats, _
                                          culture, _
                                          DateTimeStyles.AllowWhiteSpaces)
      Console.WriteLine("3) myDateTimeFrench = {0}", myDateTimeFrench)
   End Sub 'Main
End Class 'Class1


0
 
jnhorstCommented:
Could you post exactly what your Label control displays?

John
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
pbisseggerAuthor Commented:
Here is what the label control - Gettingstarted.text - displays:
   Datetime: 10/25/2006 12:15:00 PM

Here is my error when I run it:
   Incorrect syntax near '12'. An unhandled exception occurred during the execution of the current web request.  
   Please review the stack trace for more information about the error and where it originated in the code. This
   happens on the line marked with a **** below.

on occasion I also get the following error
   Conversion from string "10/25/2006 ::00 PM" to type 'Date' is not valid. This happens on the line marked with a
   #### below.


    Sub AddGoal(ByVal Sender As Object, ByVal E As EventArgs)

        Dim A1, A2 As Integer
        Dim MyDate, MyTime, Whichperiod, DT, Shoe As String
        Dim MyDateTime As New DateTime
       
        'Get the game date
        cmd.Connection = MyConn
        cmd.CommandText = "SELECT convert(varchar,gamedatetime,101) as Gamedate FROM games WHERE gamenum = " & Session("Resultsgamenum")
        cmd.Connection.Open()
        MyDate = cmd.ExecuteScalar()
        cmd.Connection.Close()      

       ' Get the time of the event
            MyTime = Scoretime.Text & ":" & Scoretime1.Text & ":00 PM"
            Whichperiod = Periodnum.SelectedItem.Value

        ' Convert to Datetime
       
        #### MyDateTime = CType(MyDate & " " & MyTime, DateTime)
        Gettingstarted.Text = " Datetime: " & MyDate & " " & MyTime
       
            cmd.CommandText = "Insert into stats(Teamnum, Playernum, Gamenum, Period, Eventtime, Eventtype, Assist1, Assist2, Goaltype) Values(" & Session("ResultsHometeamnum") & ", " & Goalscorer.SelectedItem.Value & ", " & Session("ResultsGamenum") & ", '" & Whichperiod & "', " & MyDateTime & " , 'goal', " & A1 & ", " & A2 & ", '" & Goaltype.SelectedItem.Text & "')"
       
        cmd.Connection = MyConn
        cmd.Connection.Open()
        **** cmd.ExecuteNonQuery()
        cmd.Connection.Close()

     End Sub

and i also have the following HTML


                           
                            <td>
                                <asp:TextBox ID="Scoretime" runat="server" Width="20" />:<asp:TextBox
                                 ID="Scoretime1" runat="server" Width="20" />
                            </td>
                             <td>
                                <asp:DropDownList ID="Goalscorer" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum" OnSelectedIndexChanged="getAssist1" AutoPostBack="true">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Assist1" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum" OnSelectedIndexChanged="getAssist2" AutoPostBack="true">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Assist2" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Goaltype" runat="server" Width="90">
                                </asp:DropDownList>

Thanks for your help.
0
 
Roopesh_7Commented:
check the value of scoretime and scoretime1 becouse i think its not getting the value of this two....thats y it is getting 10/25/2006 ::00 PM first two fields =''  so check the value of that
0
 
Roopesh_7Commented:
during exicution check the value of mytime
0
 
jnhorstCommented:
I am curious about what 'convert(varchar,gamedatetime,101)' returns.  Could you run with a break point on MyDate = cmd.ExecuteScalar() and post just that piece?

John
0
 
pbisseggerAuthor Commented:
OK, here is an update....

1. For MyDate, the results of MyDate = trim(cmd.ExecuteScalar()) is "10/25/2006". I added in the trim().

2. The "10/25/2006 ::00 PM" only happens in cases where I do not start the process from scratch (ie: when I upload a page change and just try to reload the page and get it to work). In other words, there seems to be something that's not happening when the page is posting back.

3. I am not able to check the value of MyTime during execution, because I do not have all the components on my development computer. However, I upload the a page change and simply comment out the "cmd.ExecuteNonQuery()" statement, and the Gettingstarted.Text label is always displaying the correct string.

I have changed  the line
MyTime = Scoretime.Text & ":" & Scoretime1.Text & ":00 PM"
to
MyTime = Trim(Scoretime.Text.ToString) & ":" & Trim(Scoretime1.Text.ToString) & ":00 PM"
but it has no improvement

Finally, always the datebase problem is
"Incorrect syntax near 'XX'"
where XX is always the value in my Scoretime.Text box.

Any other ideas ?
0
 
pbisseggerAuthor Commented:
I have solved the problem.

Using MyDate from:
        cmd.CommandText = "SELECT convert(varchar,gamedatetime,101) as Gamedate FROM games WHERE gamenum = " & Session("Resultsgamenum")
        cmd.Connection.Open()
        MyDate = Trim(cmd.ExecuteScalar())
        cmd.Connection.Close()

and MyTime from:
        MyTime = Trim(Scoretime.Text.ToString) & ":" & Trim(Scoretime1.Text.ToString) & ":00PM"

MyString = "Insert into stats(Eventtime) Values('" & MyDate & " " & MyTime & "')"

Here's where I went wrong ...

a) Even though I am using a datetime field, since I am setting the Commandtext variable with my SQL query, it seems that I just needed to pass the datetime using normal strings, and never did have to do any conversions to datetime.  
b) Although using a cut and paste from an SQL datetime database field, there is a space between the Time and the AM/PM designation, in order to populate the CommandText variable, through trial and error, I found that the time and PM designation had to be adjacent (for example 12:45:56PM, notice no space between the time and the AM/PM designation).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.