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

https://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"

[NeoPa]

Remember also, that if you have no join between your tables, you will get output for every combination of all records in your tables. Is this REALLY what you want?

[Denburt]

Can't add a thing to that, nice job Ade.

[Ajay Bathija]

Ade,

Thanks for coming to the recuse.

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.

Earlier I tried the Orderby function, but it had not worked for me. I Am now sure that I had made some stupid mistake in the snytax.

Ade, the solution that you have provided works for me and can meet the minimun requirements of my project atleast.

The limitation of this method would be that the user cannot Sort the Datagridview while the application is running. Not sure if there is a way to do that while maintaing the order. If there is a way then I would highly appreciate if you could let me know.

Remember also, that if you have no join between your tables, you will get output for every combination of all records in your tables. Is this REALLY what you want?

Ade, I also want to follow good coding techinques.

At this point of time, I am pulling data from two tables. From the first table I am choosing the records based on "mstrScheduleID " and "mstrPeronI D " combination. The records that are pulled have a "roleId" which corresponds to a "Role name"

Is there a way to do this using the Join function?

Someone had suggested the current method because I was using Access as the backend.

Ajay Bathija

https://www.ajaybathija.com

[NeoPa]

Ade,

Thanks for coming to the rescue.

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.

Earlier I tried the Orderby function, but it had not worked for me. I Am now sure that I had made some stupid mistake in the snytax.

Ade, the solution that you have provided works for me and can meet the minimun requirements of my project atleast.

The limitation of this method would be that the user cannot Sort the Datagridview while the application is running. Not sure if there is a way to do that while maintaing the order. If there is a way then I would highly appreciate if you could let me know.

Remember also, that if you have no join between your tables, you will get output for every combination of all records in your tables. Is this REALLY what you want?

Ade, I also want to follow good coding techniques.

At this point of time, I am pulling data from two tables. From the first table I am choosing the records based on "mstrScheduleID " and "mstrPeronI D" combination. The records that are pulled have a "roleId" which corresponds to a "Role name"

Is there a way to do this using the Join function?

Someone had suggested the current method because I was using Access as the backend.

Ajay Bathija
www.ajaybathija.com

I would need a much clearer explanation of what you're trying to do to answer this question.
What is your Table MetaData for starters?
Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Please try to make sure that all names are spelt correctly. Typos can cause all sorts of problems and waste so much of everybody's time.

[Ajay Bathija]

I would need a much clearer explanation of what you're trying to do to answer this question.
What is your Table MetaData for starters?

Ade, I apologize if I mis-spelled anything.
The metadata information that you requested.

Table Name = ScheduleItem

ScheduleItemID; AutoNumber, PK
ScehduleID, Number, Fk
PersonID, Number, FK
StartTime, Date/time
EndTime, Date/Time
RoleID, Number, FK

Table Name = racRoles

RoleID, Autonumber, PK
RoleName, Text

While responding to you, I realized that I have mis-spelled "ScehduleID " in Table "ScheduleIt em", throughout the code. I will rectify this issue later.

Algorithm of my program is
1. User provides the "ScehduleID " via the variable "mstrScheduleID "
2. User provides the value for "PersonID" via variable "mstrPeronI D"
3. For this combination (ScehduleID and PersonID) I pull all the records from the table "ScheduleIt em"
4. The "RoleID" (number) for each record pulled, should be placed by the corresponding "RoleName" (text) from the table "racRoles"
5. Display this information in the Datagrid.

Please let me know if you need anything else in specific.

Thanks.
Ajay