I need to Converting to 24 Hour Time and include Seconds 1

[daver56]

I have a project where I need to convert a time into military time. I cannot use the Short Time format, as I need to display hour, minutes, an seconds. I have searched here and other places on the web.

Thanks in advance

Dave

 

[JerryKlmns]

Change the formatting as you like

SELECT Format([YourDateField], "yyyy-mm-dd hh:nn:ss")
FROM YourTable

 

[daver56]

Jerry,

Thanks for the reply. I did get the format to display correctly on the query, and on an associated report, by changeing the format in which my PC displayed the time.

I cannot change the format in the table though. It is a linked table that is used by our main software application. Changing the field format would cause problems there I am afraid. I am linking to those tables to do some custom reporting.

Thanks

Dave

 

[SkipVought]

Hi,

Dates are Dates. Formats have nothing to do with the underlying data.

For instance, today's Date value is 39098. It can be FORMATTED in literally dozens of ways.

Skip,
[sub]
[red][/red]
[/sub]

 

[daver56]

Skip,

I realize that, but the table I am linked to is storing the date as a text field, not as a date field. Why, I do not know. Instead of using a Date/Time field, they use a text field for the date and another one for the time. Each record has several times recorded, and for each one they use a date and time field in text format.

I am using DateValue, and TimeValue and combining the two to get back to a regular Date/Time format in my query. Then I am subtracting the earlier from the later, to come up with a time interval.

 

[SkipVought]

"I realize that, but the table I am linked to is storing the date as a text field, not as a date field. "

Do you think that this was an IMPORTANT piece of information to withhold this long?

It might ALSO be important to post several example of this TEXT field, in order to know how one should go about converting this string to a real Date/Time value.

Skip,
[sub]
[red][/red]
[/sub]

 

[daver56]

I did not think it important since I alread had converted the data in the query for a report. My Bad...

To answer you other question, the two fields they are using look like the examples below.

2007-01-16 and for the time 14:44:30 The date field is text type field with a length of 10 and the time is a text with a length of 8.

I am using the example below to convert to date/time and then subtract.

Expr1: (DateValue([begindate])+TimeValue([begintime]))-(DateValue([enddate])+TimeValue([endtime]))

This may not be the best way to do this but it appears to work.

I had looked at DateDiff, but it did not appear to provide me with the time difference down to the second.



I am an Access Beginner

Dave

 

[SkipVought]

I think that you might want to subtract the beginning date/time from the ending date/time.

Skip,
[sub]
[red][/red]
[/sub]

 

[daver56]

I reversed them by mistake when I re-typed them on the post..... you are correct though. I did state it correctly in an earlier post.

I do have aother question though.

In the same project I have another issue.

I have a table that is the schedule for a given day of two employees that are on a shift. That table contains a schedule number a date, the begining and ending time and a number for each of two employees. The value of each employee is a number that is the key field in another table where there is a record for each employee.


for example

the main table list a schedule number which contains among other things the date, time, location and etc.

The main table references the schedule table, and the schedule table references the employee table.

In my query I looking for certian incidents and when I find them I need to be able to display them and then use the query for a report. The issue I am having is listing both employees in the query.

The main table might give schedule 1453 and schedule would say that employee1 was 323 and employee2 was 224. Then the employee table would list 323 as John, and 224 as Bill.

I might should move this qestion to a new thread.

Thanks in advance.

Dave

 

[SkipVought]

It makes a difference if the 2 EmpIDs are in ONE field or TWO fields.

Skip,
[sub]
[red][/red]
[/sub]

 

[daver56]

They are in two fields in the schedule table. employee1 and employee2.

the record might look like this

2007-01-15 08:00:00 17:00:00 323 224

Where the fields are...

SchedDate, BeginTime, EndTime, Employee1, Employee2



Dave

 

[PHV]

Use 2 instances of the employee table with aliases.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daver56]

I'll give it a try. Thanks

Dave

 

[daver56]

That worked great.

Thanks

Dave