Convert Military Time to Standard

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert Military Time to Standard

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-04-09 : 18:03:59

Hello,

I have a database field that is just an appointment time but it is stored as a char and it appears to be in military time as well.

For reporting purposes I am creating a query that will use the appointment time and I need it to display in standard time format. I am doing this report in crystal and I tried to use crystal tools but it crystal recognizes it as a string so it won't let me set the time format. Is their a cast or convert or some type of sql function I can use to select the appointment time in standard time format? I have been searching online and just haven't found anything yet.

Thanks in advance!

Sherri

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-09 : 18:08:55

and the military time format would be?
you can always do:

select convert(datetime, yourDateColumn)
from yourTable

and then format that datetime in whatever way you want in the report

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

sross81
Posting Yak Master

228 Posts

Posted - 2008-04-09 : 18:25:41

Thank you for the reply. Everytime I try to use any type of datetime convert it tells me that the conversion of a char data type to a datetime data type resulted in an out of range datetime value. There is no date in the field it is just a time so for example it reads 1410 and I want it to say 2:10 P.M. Any thoughts? Thanks in advance!

Sherri

bfoster
Starting Member

30 Posts

Posted - 2008-04-09 : 18:58:29

If you just want to return a string you could do something like this. You should add another condition if you willhave any midnight times.

DECLARE @Military TABLE(MT CHAR(4))

SELECT
CASE
WHEN Hour24 > 12 THEN CONVERT(varchar, Hour24 % 12) + ':' + Minute + ' P.M.'
WHEN Hour24 = 12 THEN CONVERT(varchar, Hour24) + ':' + Minute + ' P.M.'
ELSE CONVERT(varchar, Hour24) + ':' + Minute + ' A.M.'
END
FROM
(
SELECT CAST(LEFT(MT, 2) AS INT) AS Hour24, RIGHT(MT, 2) AS Minute
FROM @Military
) a

sross81
Posting Yak Master

228 Posts

Posted - 2008-04-10 : 10:17:31

I want to try that out but I put it into my code and it told me I had to declare @Military. I am not using a stored procedure for this as I don't have access to SQL. I am just writing the query through the sql command builder in crystal. Here is my code:

SELECT a.first_name, a.last_name,cast(a.date_of_birth as datetime) as DOB, cast( b.appt_date as datetime) as ApptDate,b.begintime, c.location_name, d.description, e.event
FROM person a
JOIN appointments b ON a.person_id = b.person_id
JOIN provider_mstr d ON b.rendering_provider_id = d.provider_id
JOIN location_mstr c ON b.location_id = c.location_id
JOIN events e ON b.event_id = e.event_id

In the beginning the c.begintime is the time field that I need to convert. I tried declaring the variable at the beginning of the query and then adding the conversion to the end but it said to declare @military so I must be using it wrong. Could someone tell me where to insert this code to make this conversion? Thanks so much in advance!! :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 11:22:22

If your time is stored as a 4 character string in format HHMM, this shows how to convert it to a datetime. You can then use front-end formatting in Crystal to display it the way you want.


select
	MyTime,
	MyDatetime = convert(datetime,stuff(a.MyTime,3,0,':'))
from
	(
	-- Test Data
	select MyTime = '0000' union all
	select MyTime = '0245' union all
	select MyTime = '1410' union all
	select MyTime = '2359'
	) a


Results:
MyTime MyDatetime
------ ------------------------
0000   1900-01-01 00:00:00.000
0245   1900-01-01 02:45:00.000
1410   1900-01-01 14:10:00.000
2359   1900-01-01 23:59:00.000

(4 row(s) affected)

CODO ERGO SUM

sross81
Posting Yak Master

228 Posts

Posted - 2008-04-10 : 14:52:54

Thanks for the reply. I inputted it like this:

SELECT a.first_name, a.last_name,cast(a.date_of_birth as datetime) as DOB, cast( b.appt_date as datetime) as ApptDate,b.begintime, c.location_name, d.description, e.event
FROM person a
JOIN appointments b ON a.person_id = b.person_id
JOIN provider_mstr d ON b.rendering_provider_id = d.provider_id
JOIN location_mstr c ON b.location_id = c.location_id
JOIN events e ON b.event_id = e.event_id

select
b.begintime,
b.begintime = convert(datetime,stuff(b.begintime,3,0,':'))
from
(
-- Test Data
select b.begintime = '0000' union all
) b

It tells me their is an error near "=". Can you see what I am doing wrong. I am not sure if I am using your code correctly??

bfoster
Starting Member

30 Posts

Posted - 2008-04-10 : 15:22:19

1) To see my example work with you data, I think you could run this query, but it just shows you how it would work. It would still need to be integrated into your query.

SELECT
CASE
WHEN Hour24 > 12 THEN CONVERT(varchar, Hour24 % 12) + ':' + Minute + ' P.M.'
WHEN Hour24 = 12 THEN CONVERT(varchar, Hour24) + ':' + Minute + ' P.M.'
ELSE CONVERT(varchar, Hour24) + ':' + Minute + ' A.M.'
END
FROM
(
SELECT CAST(LEFT(begintime, 2) AS INT) AS Hour24, RIGHT(begintime, 2) AS Minute
FROM appointments
) a

2) I think this will show you how the last suggestion would work with your data.

SELECT a.first_name, a.last_name,cast(a.date_of_birth as datetime) as DOB, cast( b.appt_date as datetime) as ApptDate,convert(datetime,stuff(b.begintime,3,0,':')) as begintime, c.location_name, d.description, e.event
FROM person a
JOIN appointments b ON a.person_id = b.person_id
JOIN provider_mstr d ON b.rendering_provider_id = d.provider_id
JOIN location_mstr c ON b.location_id = c.location_id
JOIN events e ON b.event_id = e.event_id

3) The problem near the "=" error is probably due to the "union all" that is no longer needed.

sross81
Posting Yak Master

228 Posts

Posted - 2008-04-10 : 17:04:40

Yay!!! Its working. Thanks so much for showing it to me inside my code example. I am still kind of new to sql programming. I will definitely be saving this example for future projects!! Again thank you so much to everyone that helped me through this I really appreciate it alot!!! :)

andros30
Yak Posting Veteran

80 Posts

Posted - 2011-06-28 : 16:29:35

I have the same situation but my field stores 6 character hhmmss.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources