473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 7860
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,556 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,556 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,556 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,556 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,556 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,556 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,556 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

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

Similar topics

4
by: Brian Coy | last post by:
I have a database that records a part no, a scrap reason, the cost of scrapping that item, and the date. I need to porduce a report that will show up to a weeks worth of data at a time, but I need...
3
by: Agnes | last post by:
I know how to display Date only in the textbox . but How about the time ??? Can I put it into two separate textbox , but save in one datafield ? thanks a lot.
3
by: LurfysMa | last post by:
I would like to hear opinions on the tradeoffs of putting the tables, forms, and queries for several related datasets in separate databases vs one combined database. I am working on an...
3
by: colleen1980 | last post by:
Hi: Data in my table is in that format. How to i separate date with time. 11/9/2006 10:10:46 AM Thank You.
2
by: drurjen | last post by:
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 ...
9
by: tshad | last post by:
This was posted before but the message got messed up (all NLs were stripped out for some reason). I have 2 labels that hold the name of different images on my .aspx page. <asp:Label ID="Logo"...
3
by: salmobytes | last post by:
Every img tag (<img src="xxx.jpg">) in the html source represents a separate GET roundtrip between client and server, no? What about background images in css? I'm starting to see more and more...
0
by: Yasin | last post by:
Walt <walt@boatnerd.com.invalidwrote in message news:<3F0C5BA9.DB809458@boatnerd.com.invalid>... You can use below sql for your result select * from test; PERSON_ID DAT TIME CODE...
13
by: dizzydangler | last post by:
Just a quick question...I'm running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.