Varchar to Military Time Conversion

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

General SQL Server Forums

New to SQL Server Programming

Varchar to Military Time Conversion

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-12 : 09:15:53

Hi,

I have a column in the database which is of type varcvhar and the values are as follows:
05:03p
07:00a
09:00p

I need to convert these varchar to military time format like:
17:03
07:00
21:00

I cannot apply custom convert function as I just have a and p as suffix. If I had am or pm it would have been straight coversion. Need guidance/suggestion on the approaxch to resolve this.

Thank you,

Dp

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:21:26

Store the time in a DATETIME or TIME datatype column - not varchar - then all these sorts of problems go away

DECLARE @temp VARCHAR(10)
SET @temp = '05:03p'
SELECT CONVERT(datetime, @temp + 'm')

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:22:48

Formatting should be in the front end, but failing that you can convert a DATETIME to military time using:

SELECT CONVERT(varchar(5), CONVERT(datetime, @temp + 'm'), 114)

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-12 : 09:49:27

Thank you Kristen. This helped.

Dp

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:52:32

Webfred: did you delete your embarrassing REPLACE code example?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-13 : 10:14:40

Power of implicit conversion in SQL Server

DECLARE @temp VARCHAR(10)
SET @temp = '05:03p'
select dateadd(second,0,@temp+'m')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources