Need to convert military time to civilian time

[surfbum3000]

I ran a make table query that produced a table with StartTime and EndTime as text fields. When I change the data type to date/time, all data is deleted upon conversion. I would like to change the time format HHMMSS to HH:MM. The addtion of AM and PM would be a plus but not required.

 

[SQLBI]

hi,

Have you tried using FORMAT(YourTime, "hh:mm&quot in your query? where YourTime is the military value.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[Ed2020]

Public Function TimeConvert(ByVal strMilitaryTime As String) As String

TimeConvert = Left$(strMilitaryTime, 2) & ":" & Mid$(strMilitaryTime, 3, 2) & ":" & Right$(strMilitaryTime, 2)

End Function

Please do not feed the trolls.....

 

[surfbum3000]

leighmoore (Programmer)

I entered Format("HHMMSS","Short Time&quot into the criteria for the StartTime field in the query. This produced no results.

Ed2020 (Programmer)

Sorry, I am new to Acceess. Where do I insert this code?

 

[SQLBI]

Hi,

If you're using a query, in the SQL statement you've probably got something like:

SELECT feld1, field2, YourTimeField, Field4...

Try Changing this to:

SELECT feld1, field2, FORMAT(YourTimeField, "hh:mm"), Field4...

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[surfbum3000]

Would this code be correct for the time conversion?

SELECT [SCEVENT ].UNIT_ID, [SCEVENT ].CLIENT_ID, tblCaseNosIDs.CASE_NUM, tblCaseNosIDs.SORT_NAME, [SCEVENT ].STARTDATE, [SCEVENT ].ENDDATE,

[SCEVENT ].FORMAT(STARTTIME, "hh:mm&quot,[SCEVENT ].FORMAT (ENDTIME, "hh:mm&quot,

[SCEVENT ].SUBJECT, tblCaseNosIDs.HOME_PHONE INTO tblSCEVENTWithDate
FROM [SCEVENT ] INNER JOIN tblCaseNosIDs ON [SCEVENT ].CLIENT_ID = tblCaseNosIDs.ID
WHERE ((([SCEVENT ].STARTDATE)>#10/24/2003#) AND (([SCEVENT ].ENDDATE)<#11/28/2003#) AND (([SCEVENT ].SUBJECT) Like &quot;*intake*&quot)
ORDER BY [SCEVENT ].STARTDATE, [SCEVENT ].ENDDATE;

 

[SgtJarrow]

First try:

SELECT feld1, field2, FORMAT(YourTimeField, &quot;Long Time&quot, Field4...

If not successful:

SELECT feld1, field2, FORMAT(YourTimeField, &quot;Hh:Nn:Ss&quot, Field4...

the first will provide the system defined long time and the second will provide hours:minutes:seconds but no am/pm indicator


****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[surfbum3000]

The following code produced this error: Undefined function '[SCEVENT].FORMAT' in expression. When I added .FORMAT(STARTTIME,&quot;Long Time&quot Access added AS Expr1.

[SCEVENT ].FORMAT(STARTTIME,&quot;Long Time&quot AS Expr1, [SCEVENT ].FORMAT(ENDTIME,&quot;Long Time&quot AS Expr2,

 

[SgtJarrow]

it would have to be:

FORMAT([SCEVENT ].STARTTIME,&quot;Long Time&quot AS Expr1, FORMAT([SCEVENT ].ENDTIME,&quot;Long Time&quot AS Expr2,

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[CajunCenturion]

If it were me, I would take Ed2020's function one step further:

Public Function TimeConvert(ByVal strMilitaryTime As String) As String

TimeConvert = Format(Left(strMilitaryTime, 2) & ":" & Mid(strMilitaryTime, 3, 2) & ":" & Right(strMilitaryTime, 2), "HH:MM AMPM")

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PaulBricker]

You can put this right in the query.

ConvertedMilitaryateAdd(&quot;n&quot;,DatePart(&quot;n&quot;,[DateField]),DateAdd(&quot;h&quot;,DateDiff(&quot;h&quot;,#12:00:00 PM#,[DateField]),#12:00:00 PM#))

Paul

 

[surfbum3000]

You can put this right in the query.

ConvertedMilitaryateAdd(&quot;n&quot;,DatePart(&quot;n&quot;,[DateField]),DateAdd(&quot;h&quot;,DateDiff(&quot;h&quot;,#12:00:00 PM#,[DateField]),#12:00:00 PM#))

I'm lost. Where do I put this in the query?
===================================
Public Function TimeConvert(ByVal strMilitaryTime As String) As String

TimeConvert = Format(Left(strMilitaryTime, 2) & &quot;:&quot; & Mid(strMilitaryTime, 3, 2) & &quot;:&quot; & Right(strMilitaryTime, 2), &quot;HH:MM AMPM&quot

End Function

I don't know where to put this either! Sorry.

 

[CajunCenturion]

You could try something like the following:

UPDATE <TableName>
SET <MilitaryTimeField> = TimeConvert(<MilitaryTimeField>);

Back up the table first, but that should convert all of the HHMMSS 24 hour times to 12 hour HH:MM AM/PM times.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PaulBricker]

Just copy and paste the expression right to the Field line in your query. You will need to change [DateField] to the name of your date field. That will do it.

Paul