?
Solved

VB.NET string to datetime

Posted on 2006-10-27
9
Medium Priority
?
1,975 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

0
Comment
Question by:pbissegger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 7

Accepted Solution

by:
whityum earned 200 total points
ID: 17821571
CType(MyDate & " " & MyTime, DateTime)
0
 
LVL 6

Expert Comment

by:Roopesh_7
ID: 17821648
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
 
LVL 10

Expert Comment

by:jnhorst
ID: 17822530
Could you post exactly what your Label control displays?

John
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pbissegger
ID: 17829209
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
 
LVL 6

Assisted Solution

by:Roopesh_7
Roopesh_7 earned 200 total points
ID: 17829282
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
 
LVL 6

Expert Comment

by:Roopesh_7
ID: 17829286
during exicution check the value of mytime
0
 
LVL 10

Assisted Solution

by:jnhorst
jnhorst earned 100 total points
ID: 17829407
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
 

Author Comment

by:pbissegger
ID: 17836548
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
 

Author Comment

by:pbissegger
ID: 17883122
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question