Problem converting military time from CHAR column

Hi all,

I have a time column in CHAR(4) format. The contents are stored as 'military time':

1800

1830

2130

I tried using various functions, but was not able to convert to standard time.

I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:

2006-05-31 06:30 PM

Any help would greatly be appreciated.

Thanks.

- gshaf

Your military time conversion wont work because you dont have a colon separating your hours and minutes.

Try this statement:
PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)

Try this one instead, this will add a zero to your hour:

IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END

Which prints out
2006-05-31 09:30PM

Your time column is going to need that colon. Otherwise your military times will not be output properly.

|||

Worked! Just needed to correct the formatting.

Thanks so much Elliot!

- gshaf