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

[MMcCarthy]

Try this ...

Try
 
            myQuery = "SELECT Format(Tb1.StartTime, "hh:nn A.M./P.M."), Format(Tb1.EndTime, "hh:nn A.M./P.M."), 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

Mary

[Ajay Bathija]

Mary:

Thanks for the help. I highly appreciate it.

You said:

>>Try this ...

myQuery = "SELECT Format(Tb1.StartTime, "hh:nn A.M./P.M."), Format(Tb1.EndTime, "hh:nn A.M./P.M."), 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"

First, I hoping that I am not missing anything, the only change you suggested was in "Myquery".

I tried using the code that you provided, but vb.net would not accept it, as it was getting confused in the "s being used.

Hence I tried the following:

myQuery = "SELECT format(Tb1.StartTime," + Chr(34) + "hh:nn AM/PM" + Chr(34) + ") as StartTime, format(Tb1.EndTime," + Chr(34) + "hh:nn AM/PM" + Chr(34) + "), 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"

The Sorted datagridview result was:
01:00 PM
05:30 PM
09:00 AM
12:00 PM
12:30 PM

Based on the above I am assuming that the query returns a string, which causes a problem in sorting. Is there a way I can solve this sorting problem easily.

I need to make sure that the time displayed in the datagrid is sorted properly.

[MMcCarthy]

Single quotes will solve that problem. Try this although it may just put the date back in.

myQuery = "SELECT CDate(Format(Tb1.StartTime,'hh:nn AM/PM')) as StartTime, CDate(Format(Tb1.EndTime,'hh:nn AM/PM')), 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"

[Ajay Bathija]

Try this although it may just put the date back in.

Mary, thanks for the Single quote information.
I tried the Cdate technique, it inserted the Date (12/30/1899 9:00 AM)

[MMcCarthy]

Mary, thanks for the Single quote information.
I tried the Cdate technique, it inserted the Date (12/30/1899 9:00 AM)

Thought that might happen

[MMcCarthy]

Try this ...

myQuery = "SELECT TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) as StartTime, TimeValue(Format(Tb1.EndTime,'hh:nn AM/PM')), Tb2.Rolename FROM ScheduleItem as Tb1, racRoles as Tb2 WHERE ScehduleID=" + mstrScheduleID + " AND PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId order by TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) ASC"

Mary

[Ajay Bathija]

Try this ...

myQuery = "SELECT TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) as StartTime, TimeValue(Format(Tb1.EndTime,'hh:nn AM/PM')), Tb2.Rolename FROM ScheduleItem as Tb1, racRoles as Tb2 WHERE ScehduleID=" + mstrScheduleID + " AND PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId order by TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) ASC"

Mary

Same result, the date is being inserted.

Based on the techniques that we have used, my understanding is that Access is not the one adding the date. The vb.net or the ole Adapter is adding the date.
If I use the simple query for just pulling the time directly (wihtout any formating) in access then it returns the time with no date. When I use that query in the vb.net code then Date is inserted.


I can think of the possible solutions but dont know how to implement them.

1. When the date is pulled form the access and stored in the datasource. I change it,that is remove the date section in the data source itself.

2. While displaying it the data gridview, I display on the time section.

Do you think any of the above listed solutions is possible?

OR would you suggest any other method of displaying this information to the users.

[Ajay Bathija]

Based on the techniques that we have used, my understanding is that Access is not the one adding the date. The vb.net or the ole Adapter is adding the date.
If I use the simple query for just pulling the time directly (wihtout any formating) in access then it returns the time with no date. When I use that query in the vb.net code then Date is inserted.

The above diagonsis seems to be incorrect.
http://www.thescripts.com/forum/thread347463.html

It is Access that is causing the problem I guess.

They have used the "format" technique which did not work in my case due to sorting.

Ajay Bathija
www.ajaybathija.com

[NeoPa]

The above diagonsis seems to be incorrect.
http://www.thescripts.com/forum/thread347463.html

It is Access that is causing the problem I guess.

They have used the "format" technique which did not work in my case due to sorting.

Ajay Bathija
www.ajaybathija.com

Ajay,
From the SQL posted earlier :

"SELECT TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) as StartTime, TimeValue(Format(Tb1.EndTime,'hh:nn AM/PM')), Tb2.Rolename " & _
"FROM ScheduleItem as Tb1, racRoles as Tb2 " & _
"WHERE ScehduleID=" + mstrScheduleID + " AND PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId " & _
"ORDER BY TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) ASC"

You will have problems as you are sorting by the formatted time string. You need to go back to sorting by the field itself. You can SELECT whatever you like, but the sorting should be done by the Date/Time field itself for the results you want.

"SELECT Format(Tb1.StartTime,'hh:nn AM/PM') AS StartTime, " & _
"Format(Tb1.EndTime,'hh:nn AM/PM') AS 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"