[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Time View in Datagrid looks like 09:00:00.0000000

Posted on 2014-07-14
14
Medium Priority
?
147 Views
Last Modified: 2014-07-28
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?
0
Comment
Question by:al4629740
13 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40195113
format the data using format (field,"hh:mm")
0
 

Author Comment

by:al4629740
ID: 40195170
How would I just do one column in the datagrid
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40196555
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
Industry Leaders: 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!

 

Author Comment

by:al4629740
ID: 40205125
Does that work for a datagrid?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40205263
That's what I tested it with. Have you had any difficulty in implementing the suggestion?
0
 

Author Comment

by:al4629740
ID: 40205360
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40205596
That's because it's a property of the column:
DataGrid1.Columns(1).NumberFormat = "hh:mm:ss.ss"

Open in new window

0
 

Author Comment

by:al4629740
ID: 40205746
This

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

gives me

19:00:00.0000000

I would rather get 9:00 PM
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40206131
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
 

Author Comment

by:al4629740
ID: 40206635
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40207034
I don't have MSSQL installed. I was checking with an Access Date/Time field
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40208102
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
 

Author Comment

by:al4629740
ID: 40209300
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

834 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