469,306 Members | 1,850 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

How to Separate Time from Date

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.

Expand|Select|Wrap|Line Numbers
  1. Try
  2.  
  3.  
  4.             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"
  5.             Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(myQuery, myConnection)
  6.  
  7.             Try
  8.                 da.Fill(ds, "Schedule")
  9.         Finally
  10.                 da.Dispose()
  11.             End Try
  12.             Return ds
  13.         Finally
  14.             myConnection.Close()
  15.             myConnection.Dispose()
  16.         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),Minute(starttime),Secon d(starttime)) This also resulted with a date inserted.

Thank for your help in advance.

Ajay Bathija
www.ajaybathija.com
Apr 12 '07 #1
20 7516
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Try
  2.  
  3.             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"
  4.             Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(myQuery, myConnection)
  5.  
  6.             Try
  7.                 da.Fill(ds, "Schedule")
  8.         Finally
  9.                 da.Dispose()
  10.             End Try
  11.             Return ds
  12.         Finally
  13.             myConnection.Close()
  14.             myConnection.Dispose()
  15.         End Try
Mary
Apr 13 '07 #2
Mary:

Thanks for the help. I highly appreciate it.

You said:
>>Try this ...
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 13 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Single quotes will solve that problem. Try this although it may just put the date back in.

Expand|Select|Wrap|Line Numbers
  1. 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"
Apr 13 '07 #4
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)
Apr 13 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
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
Apr 13 '07 #6
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 13 '07 #7
Try this ...

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 13 '07 #8

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
Apr 13 '07 #9
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. "SELECT TimeValue(Format(Tb1.StartTime,'hh:nn AM/PM')) as StartTime, TimeValue(Format(Tb1.EndTime,'hh:nn AM/PM')), Tb2.Rolename " & _
  2. "FROM ScheduleItem as Tb1, racRoles as Tb2 " & _
  3. "WHERE ScehduleID=" + mstrScheduleID + " AND PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId " & _
  4. "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.
Expand|Select|Wrap|Line Numbers
  1. "SELECT Format(Tb1.StartTime,'hh:nn AM/PM') AS StartTime, " & _
  2. "Format(Tb1.EndTime,'hh:nn AM/PM') AS EndTime, " & _
  3. "Tb2.Rolename " & _
  4. "FROM ScheduleItem as Tb1, racRoles as Tb2 " & _
  5. "WHERE ScehduleID=" + mstrScheduleID + " AND " & _
  6. "PersonID=" + mstrPeronID + " And tb1.RoleID=tb2.RoleId " & _
  7. "ORDER BY Tb1.StartTime ASC"
Apr 16 '07 #10
NeoPa
32,173 Expert Mod 16PB
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?
Apr 16 '07 #11
Denburt
1,356 Expert 1GB
Can't add a thing to that, nice job Ade.
Apr 16 '07 #12
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 "mstrPeronID " 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
Apr 16 '07 #13
NeoPa
32,173 Expert Mod 16PB
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 "mstrPeronID" 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. 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.
Apr 17 '07 #14
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
Expand|Select|Wrap|Line Numbers
  1. ScheduleItemID; AutoNumber, PK
  2. ScehduleID, Number, Fk
  3. PersonID, Number, FK
  4. StartTime, Date/time
  5. EndTime, Date/Time
  6. RoleID, Number, FK
Table Name = racRoles
Expand|Select|Wrap|Line Numbers
  1. RoleID, Autonumber, PK
  2. RoleName, Text
While responding to you, I realized that I have mis-spelled "ScehduleID" in Table "ScheduleItem", 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 "mstrPeronID"
3. For this combination (ScehduleID and PersonID) I pull all the records from the table "ScheduleItem"
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
Apr 17 '07 #15
NeoPa
32,173 Expert Mod 16PB
...
1. User provides the "ScehduleID" via the variable "mstrScheduleID"
2. User provides the value for "PersonID" via variable "mstrPeronID"
...
Ajay,
  1. Are "mstrScheduleID" & "mstrPeronID" both controls on a form?
  2. Is "mstrPeronID" correctly spelled? Maybe it should be "mstrPersonID"?
BTW Good job with your earlier post. Questions answered fine and info good :)
Just a couple of questions left then I'm sure we can progress.
Apr 18 '07 #16
Ajay,
  1. Are "mstrScheduleID" & "mstrPeronID" both controls on a form?
  2. Is "mstrPeronID" correctly spelled? Maybe it should be "mstrPersonID"?
A. Ade, In the class they are declared as:
Expand|Select|Wrap|Line Numbers
  1. Dim mstrScheduleID AS String
  2. Dim mstrPeronID AS String
Both of the above variables will always have a numeric value in the string format.
The values for these variables are pulled from other tables in the database depending on who is using the system and on which schedule there are.

B. Ade, I just realized that this is the mistake made throughout the code. I will rectify it once we have resolved the current issue. While responding to this forum I am copying and pasting all the variables to make sure that there are not typing errors.

BTW Good job with your earlier post. Questions answered fine and info good :)
Just a couple of questions left then I'm sure we can progress.
Thanks, Ade. :)
Let me know if you need any other specific information. Not sure if the ER diagram or the entire code for the class will be helpful. If yes, then please let me know how I can post the ER diagram.

Thanks.
Ajay Bathija
Apr 18 '07 #17
NeoPa
32,173 Expert Mod 16PB
Assuming :
  1. Each ScheduleItem.RoleID has a match in racRoles.
  2. mstrScheduleID & mstrPeronID are both accessible from code simply as they are.
Expand|Select|Wrap|Line Numbers
  1. "SELECT Format(Tb1.StartTime,'hh:nn AM/PM') AS StartTime, " & _
  2. "Format(Tb1.EndTime,'hh:nn AM/PM') AS EndTime, " & _
  3. "Tb2.Rolename " & _
  4. "FROM ScheduleItem as Tb1 INNER JOIN racRoles as Tb2 " & _
  5. "WHERE ScehduleID='" + mstrScheduleID + "' AND " & _
  6. "PersonID='" + mstrPeronID + "' " & _
  7. "ORDER BY Tb1.StartTime ASC"
Apr 19 '07 #18
NeoPa
32,173 Expert Mod 16PB
...Although personally, I'd format the time as a 24-hour value and lose the AM/PM stuff.
Expand|Select|Wrap|Line Numbers
  1. "SELECT Format(Tb1.StartTime,'HH:nn') AS StartTime, " & _
  2. "Format(Tb1.EndTime,'HH:nn') AS EndTime, " & _
  3. "Tb2.Rolename " & _
  4. "FROM ScheduleItem as Tb1 INNER JOIN racRoles as Tb2 " & _
  5. "WHERE ScehduleID='" + mstrScheduleID + "' AND " & _
  6. "PersonID='" + mstrPeronID + "' " & _
  7. "ORDER BY Tb1.StartTime ASC"
Apr 19 '07 #19
Ade:

Thanks for the response. Sorry for the delay.

Although personally, I'd format the time as a 24-hour value and lose the AM/PM stuff.
Ade, this is a scheduler and hence it will be inconvenient for the users to see the time in 24hr format. It will help coding but may not be good for the users. Hence I am trying to keep the time in the 12 hr format.

I tried using the code that you provided (I copied and pasted the code). While executing, it gives the error "Syntax error in FROM clause".

Does access2003 use the INNER Join functionality?
Apr 25 '07 #20
NeoPa
32,173 Expert Mod 16PB
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.
Apr 25 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Agnes | last post: by
3 posts views Thread by LurfysMa | last post: by
3 posts views Thread by colleen1980 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.