date format in a listbox (or combobox)

[Martijn van Dru]

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

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

[a_jam_sandwich]

Why dont you set the format of the date in the SQL?

 

e.g.

 

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

 

Andy

[Martijn van Dru]

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?

[dynamic_sysop]

how exactly do you want your date / time to show?

like this: 15-jun-2003

or date and time: 15-jun-2003 11:01:59

 

or like this 15/06/2003?

[Martijn van Dru]

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

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

[dynamic_sysop]

are you adding the items from a database? or clicking something?

how about this idea :

       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.

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.

[Martijn van Dru]

The dates are comming out of a database with the tablename Exp and the columnname Exp_Date_e

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

[wyrd]

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.

[a_jam_sandwich]

Yes it an access command

 

Andy

[Martijn van Dru]

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

Me.ListBox1.DataSource = Me.dsExp.Exp
       Dim myDates As DateTime = "Exp_Date_e"
       Me.ListBox1.DisplayMember = Format (myDates, "dd/mm/yyyy")

[wyrd]

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()?

[Martijn van Dru]

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

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