Converting Smalldatetime to Military Time

I am using Sql Server 2000.  I am using the following code to change a smalldatetime to a military time:

cast(DATEPART(month, a.completion_date_time) as varchar)+ '/' +
--    cast(DATEPART(day, a.completion_date_time) as varchar) + '/' +
--    cast(DATEPART(year, a.completion_date_time) as varchar) + ' ' +
--    cast(DATEPART(hh, a.completion_date_time) as varchar) +
--    cast(DATEPART(n, a.completion_date_time) as varchar)

The purpose is to make the smalldatetime a military time.  For example, change 03/10/01 06:30 to 03/10/01 0630.  This code uses the Datepart function to pick out the month, day, year, hour, and minute.  Using the cast, it changes the smalldatetime to a varchar datatype.  It concatenates the date parts into one date with a military.  

Problem:  The system omits the zeros in the hour and minute dateparts.  For example, it is suppose to be:   03/10/01 0630 instead of 03/10/01 63.

Thanks for help

BirdsOfFire1
BirdsOfFire1Asked:
Who is Participating?
 
nigelrivettConnect With a Mentor Commented:
Sorry - 2 digit year.
convert(char(9),getdate(),03) + replace(convert(varchar(5),getdate(),108),':','')
0
 
nigelrivettCommented:
convert(char(11),getdate(),103) + replace(convert(varchar(5),getdate(),108),':','')
0
 
nigelrivettCommented:
If you want to 0 pad your digits you could

right('00' + convert(varchar(2),DATEPART(month, a.completion_date_time)),2)

This is a general way to right/left justify any string in almost any language.

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
bruintjeCommented:
Hi, don't have a SQL server here but something like

Right("0" + cast(DATEPART(hh, a.completion_date_time) as varchar), 2)

should do the trick, the extended zero will drop off if hours are 9 > and will be added to hours < 10

HTH:O)Bruintje
0
 
bruintjeCommented:
oops should refresh
0
 
BirdsOfFire1Author Commented:
Thanks for answering my call and thanks to the other MS-SQL experts answering my call.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.