Converting military time - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Converting military time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-04 : 08:15:18

Keri writes "I have a military time in a char(4) field. I would like to convert it to a conventional time such as 4:30 PM. Is there any easy way to do this in SQL?"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-04 : 08:28:01

I wonder what military time looks like...

Owais

Make it idiot proof and someone will make a better idiot

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 10:49:22

Military time is just where PM is +12...so 1:00PM is really 13:00

But in char(4)?

Gotta be packed in some way....unless s/he's talking about internal storage...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 11:35:19

I assume you have it stored like this:

1105 -- 11:05 am
1430 -- 2:30 pm

??

Also, you will need some date for these times to convert them to a datetime datatype. I'll assume you want to use the "base date", or 1/1/1900.

If so,


declare @HHMM char(4)
declare @BaseDate datetime

set @HHMM = '1403'
set @BaseDate = '1/1/1900'  -- same as setting it to 0

print dateadd(hh,convert(int, left(@HHMM,2)),dateadd(mi,convert(int,right(@HHMM,2)),@BaseDate))

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 11:55:59

You know if s/he comes back they're gonna want to know how to get rid of the dae portion...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources