We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

VB.NET string to datetime

pbissegger
pbissegger asked
on
Medium Priority
2,122 Views
Last Modified: 2009-07-29
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

Comment
Watch Question

Commented:
CType(MyDate & " " & MyTime, DateTime)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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


Commented:
Could you post exactly what your Label control displays?

John

Author

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.
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
during exicution check the value of mytime
Commented:
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

Author

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 ?

Author

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).
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.