Format date from SQL into a textbox

[Mr.No]

Hi

How do I format a datetime column from a table for display in a textbox. Here's my code

Dim conn As SqlConnection
   Dim cmd As SqlCommand
   Dim connstring As String         Dim cmdstring As String
   Dim rowsaffacted As Integer
   Dim oAdapter As New SqlDataAdapter()
   Dim oTable As New DataTable()
   connstring = ConfigurationSettings.AppSettings("connectionstring")
   conn = New SqlConnection(connstring)
   Try
    conn.Open()
    cmd = New SqlCommand("SELECT * FROM vmvsurvey where id = " & _id.ToString(), conn)
    lblStatus.Text = "SELECT * FROM vmvsurvey where id = " _
                           & _id.ToString()
     oAdapter = New SqlDataAdapter(cmd)
     oAdapter.Fill(oTable)
 
     ' This is where I'm having trouble
     txtDate.Text = oTable.Rows(0).Item("surveydate")  
   Catch sqlex As SqlException
       lblStatus.Text = sqlex.ToString()
    Catch ex As Exception
       lblStatus.Text = ex.ToString()
    Finally
       With conn
          If .State <> ConnectionState.Closed Then
              .Close()
          End If
       End With
       conn = Nothing
   End Try

The date value is currently displayed as 07/02/2003 15:18:00 but I would like it to be displayed as 07/02/03. I tried many formats etc. but can't seem to find the right approach. I can bind the textbox to the datasource so that's already out.

Thanks

[Lunatic3]

you mean this does not work for you?

txtDate.Text = oTable.Rows(0).Item("surveydate").ToString("d")

[Mr.No]

Lunatic3

You're right , the code below does not work

txtDate.Text = oTable.Rows(0).Item("surveydate").ToString("d")

For the moment I am explicitly formatting the date in my SQL statement but I wish there was something more direct ..

[Lunatic3]

What about doing this?

Dim survdate as DateTime
survdate= oTable.Rows(0).Item("surveydate")
txtDate.Text=survdate.ToString("d")

For sure the formating works, but y not in your case I have no idea

[Mr.No]

Thanks a lot Lunatic3.
Here's how I finally fixed it ("d" was producing 07/02/2003 when I was looking for 07/02/03).

txtsurveydate.Text = CType(oTable.Rows(0).Item("surveydate"), DateTime).ToString("dd/MM/yy")

I would like to share some of my findings while struggling with this. It seems that the format string is case sensitive. So,
if you have a date like 07-Feb-2003 15:18 comming from the table the formats below will produced the following results.

' This will output 07/02/03
txtsurveydate.Text = CType(oTable.Rows(0).Item("surveydate"), DateTime).ToString("dd/MM/yy") 
 
' This will output 07/18/03
txtsurveydate.Text = CType(oTable.Rows(0).Item("surveydate"), DateTime).ToString("dd/mm/yy")

I was thinking that "dd/mm/yy" and "dd/MM/yy" meant the same thing


Thanks again for your help Lunatic3

[Lunatic3]

It is not strange 'Mr.No', as 'm' stands for minute. and 'M' for month. You may like to take a look at here to find more about the format strings of Date and Time.