How to Separate Time from Date

[Ajay Bathija]

Hi:

I am new to this forum. If I am not through in my explanation then please let me know.

I need help in figuring out how I can accomplish the following.

I am using VB.net for the front end application, and connecting it to the Access database

In the VB form I have a data grid. I am trying to fill the datasource of the data grid from the access table.

I have an access table (Name: Scheduleitem). It has 6 cols. Out of which there are two Date/Time cols.

Date/Time col #1 is named "Start Time"
Date/Time Col #2 is named "end time"

Entries in "Start time" Col are
9:00 AM
12:00 PM
12:30 PM
1:00 PM
5:30 PM.

In the datagrid I want Time to be displayed. I do not want to convert it to a string because the sorting gets messed up. If you know any other way to prevent the sorting from getting messed up then please let me know.

I am using the following code at this point of time.

Try
 
 
            myQuery = "SELECT Tb1.StartTime, Tb1.EndTime, Tb2.Rolename FROM ScheduleItem as Tb1, racRoles as Tb2 WHERE ScehduleID=" + mstrScheduleID + " AND PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId order by Tb1.StartTime ASC"
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(myQuery, myConnection)
 
            Try
                da.Fill(ds, "Schedule")
        Finally
                da.Dispose()
            End Try
            Return ds
        Finally
            myConnection.Close()
            myConnection.Dispose()
        End Try

This code gives me the
12/30/1899 9:00 AM
12/30/1899 12:00 PM
12/30/1899 12:30 PM
12/30/1899 1:00 PM
12/30/1899 5:30 PM

Is there a way so that I display only the time and not the date? The date ( 12/30/1899) is being inserted by the system.

I have tried the techniwue of keeping the cols as text instead of date and time. In this case sorting does not work properly in the datagrid.
Then I tried to keep the cols as date/time and used the format method, the result was a string so sorting did not work.
Then I tried using timeserial(hour (starttime),Min ute(starttime), Second(starttim e)) This also resulted with a date inserted.

Thank for your help in advance.

Ajay Bathija
www.ajaybathija .com

[NeoPa]

It certainly does Ajay.

No problem about the delay BTW. It's all part of being a forum.

Can you get us a post showing exactly what is in the string that you pass to the SQL engine. I can't see anything wrong with the FROM clause in my post. I'm assuming the tables and MetaData are as listed in post #15.