Time View in Datagrid looks like 09:00:00.0000000

When I pull up data from my Time column in the DB into my Datagrid in vb6 it looks like this:

09:00:00.0000000

I want to leave out all the 0s after 9:00.  How can I make the view better within the datagrid?
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
format the data using format (field,"hh:mm")
0
al4629740Author Commented:
How would I just do one column in the datagrid
0
GrahamSkanRetiredCommented:
Right-click on the control on the form and choose 'Properties...'.
Click on the 'Format' tab.
Chose the column in the 'Format Item:' box.

Then either:
Click on 'Time'.
Select the appropriate format from the right-hand list.

or :
Click on 'Custom'.
Create your own format string, e.g.
hh:mm:ss.ss
Will give your hours, minutes and seconds to two decimal places.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
Does that work for a datagrid?
0
GrahamSkanRetiredCommented:
That's what I tested it with. Have you had any difficulty in implementing the suggestion?
0
al4629740Author Commented:
I don't see format in the properties.  Only in custom...

I want to do it now in code.  There is no

 datagrid1.format.time

how can i do this via code?
0
GrahamSkanRetiredCommented:
That's because it's a property of the column:
DataGrid1.Columns(1).NumberFormat = "hh:mm:ss.ss"

Open in new window

0
al4629740Author Commented:
This

DataGrid1.Columns(8).NumberFormat = "hh:mm"

gives me

19:00:00.0000000

I would rather get 9:00 PM
0
GrahamSkanRetiredCommented:
My original testing seemed to work OK, but I now see that only two columns are accessible in design mode. I had forgotten that.

However it can be done in code:
DataGrid1.Columns(3).NumberFormat = "hh:mm AM/PM"

Open in new window

0
al4629740Author Commented:
Ok.  But it still shows the wrong format.  My original datatype in the table is Time(7) in the SQL database.  Could that be the reason?

It continues to have too many number like

9:00:00.0000000
0
GrahamSkanRetiredCommented:
I don't have MSSQL installed. I was checking with an Access Date/Time field
0
PortletPaulfreelancerCommented:
Not sure if you want to address this via TSQL, seems to be it should be addressed in the datagrid somehow,

Here are some ideas for TSQL. Note format() is only an option for mssql 2012 or later
|           MYTIME | COLUMN_1 | COLUMN_2 | COLUMN_3 | COLUMN_4 | COLUMN_5 |
|------------------|----------|----------|----------|----------|----------|
| 09:00:00.0000000 |   9:00AM | 09:00:00 | 09:00:00 | 09:00:00 | 09:00:00 |

    CREATE TABLE Table1
    	([MyTime] Time(7))
    ;
    	
    INSERT INTO Table1
    	([MyTime])
    VALUES
    	('09:00:00')
    ;

**Query 1**:

    select
            MyTime
          , convert(varchar,MyTime,100)
    
    -- if mssql 2012 or later:
          , format(MyTime,'t')
          , format(MyTime,'T')
          , format(MyTime,'t', 'en-US')
          , format(MyTime,'T', 'en-US')
    
    from table1
    

**[Results][2]**:
    
    |           MYTIME | COLUMN_1 | COLUMN_2 | COLUMN_3 | COLUMN_4 | COLUMN_5 |
    |------------------|----------|----------|----------|----------|----------|
    | 09:00:00.0000000 |   9:00AM | 09:00:00 | 09:00:00 | 09:00:00 | 09:00:00 |



  [1]: http://sqlfiddle.com/#!6/81c10/7

Open in new window

see: http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html

btw: if you really must have a space between 9:00 AM then you will need more
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
I agree with Paul that there must be a way to address this via vb6...Are there any ways to reformat the data type Time(7) within a datagrid?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.