date format in a listbox (or combobox)

M

Martijn van Dru

Newcomer
Joined
Jun 10, 2003
Messages
23
I read in date's from out of a database into a listbox. But they all got dislpayed in a way of: 10/17/2003 12:00:00 AM what I find very ugly
I prefer to have them displayed in a way of: 10/17/2003


dsExp is my dataset
Exp is my table in the database
and Exp_Date_e is the column with the different dates
lbExp is the listbox where I want to display the different dates
Code: 
Me.lbExp.DataSource = Me.dsExp.Exp
Me.lbExp.DisplayMember = "Exp_Date_e"


Private Sub LoadListExp()

        Me.odaExp.SelectCommand.Parameters(0).Value = Me.txtUnd_ID.Text
        Me.dsExp.Clear()
        Me.odaExp.Fill(dsExp)
How can I manipulate the format of the dates when they are displayed in a listbox?

I hope someone can help me,
Martijn
 
Why dont you set the format of the date in the SQL?

e.g.

Code: 
SELECT Id, Format([Exp_Date_e],"dd\/mm\/yyyy") AS MyDate From MyTable

Andy
 
The strange thing is that I have already the right format in my database (I work with access, and there I use for example Medium Date that has a format of 15-jun-2003). I have the feeling that the format got changed in the dataset (so after using the querry). So is it also possible to manipulate the date-format when it's already in the dataset?
 
I want to see the dates in the listbox like:15/06/2003
I don't wanna see any information about time (It is also not in de database, so it displays all the time 12:00:00 AM). I tried it the way Andy suggests, but that didn't work neither
Code: 
--------------------------------------------------------------------------------
SELECT Id, Format([Exp_Date_e],"dd\/mm\/yyyy") AS MyDate From MyTable
 
are you adding the items from a database? or clicking something?
how about this idea :
Visual Basic: 
        Dim strDate As String = "15/06/2003 10:17:56 AM"
        strDate = strDate.Split(Chr(32))(0)
        MsgBox(strDate)
splitting the date from the rest of the time info.
Visual Basic: 
Dim strExp_Date_e As String = Exp_Date_e
strExp_Date_e = strExp_Date_e.Split(Chr(32))(0)'/// chr(32) being the space " "
Me.lbExp.DataSource = Me.dsExp.Exp
Me.lbExp.DisplayMember = strExp_Date_e
not sure if this helps.
 
The dates are comming out of a database with the tablename Exp and the columnname Exp_Date_e
Code: 
Dim strExp_Date_e As String = Exp_Date_e
I tried it, but VB doesn't accept it.

There has to be an easy way. I don't understand it. In Vb the format of my data are changing, and I can't change them back when I want to display them in a Listbox.

When anabody kwnowes a solution, please let me know
 
You need to understand that the Database and Dataset columns are DateTime, thus when you insert a date like 10/17/2003 and don't specify a time, it automatically uses 12:00am (the start of the day). Likewise for times, if you insert a time but not a date, then it will add the default date (I forget off hand what it is).

Formatting DateTime is fairly easy. You can either do it in the SELECT query or after you retrieve the full DateTime.

Formatting when retrieving data:
- If you're retrieving it as a string, try using String.Format("{0:dd/MM/yyyy}", date)
- Since you're dealing with a DateTime, why not retrieve it into a DateTime object? Then you can simply do date.ToString("dd/MM/yyyy")

Search MSDN or your help files for formatting options. Tons of info on it there.

Formatting dates with SQL:
- Simply use CONVERT(char(8), date, 103) - This will return mm/dd/yy in string format (just as you want). You can read about the format codes here; http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
- There is SET DATEFORMAT which you can read about here;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_052s.asp

Note that these are SQL Server techniques. Hopefully the CONVERT will work even in Access. I tried looking up FORMAT() but couldn't find anything on it (maybe it's an Access command)

Hope this helps.
 
Formatting the Dates with the select query doesn't work.
It works when I first change it to DateTime and then display it in for example a textbox.
But now I don't know how I change a column in my my dataset (that's is a string) to a DataTime object

this code doesn't work, because I receive a cast error
Code: 
Me.ListBox1.DataSource = Me.dsExp.Exp
        Dim myDates As DateTime = "Exp_Date_e"
        Me.ListBox1.DisplayMember = Format (myDates, "dd/mm/yyyy")
 
Change

Dim myDates As DateTime = "Exp_Date_e"

to

Dim myDates As DateTime = DateTime.Parse("Exp_Date_e")

From your sample code it looks like your converting a string into a DateTime, then back into a string to format it. Why not just use String.Format()?
 
The suggested next line doesn't work

Dim myDates As DateTime = DateTime.Parse("Exp_Date_e")

it gives an error because "Exp_Date_e" is not recognised as a valid Date Time.
Now I apply the following code
Code: 
Dim arrayExp As ArrayList = New ArrayList()
        Dim row As DataRow
        For Each row In dsExp.Tables("Exp").Rows
            arrayExp.Add(Format(row("Exp_Date_e"), "dd/MM/yyyy"))
        Next
        Me.ListBox1.DataSource = arrayExp
It works, but I don't like the solution. I have the feeling that there has to be a much easier solution.

Thanks
 
Back
Top