Solved

Converting Smalldatetime to Military Time

Posted on 2001-08-30
6
1,264 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:BirdsOfFire1
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6442672
convert(char(11),getdate(),103) + replace(convert(varchar(5),getdate(),108),':','')
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6442689
Sorry - 2 digit year.
convert(char(9),getdate(),03) + replace(convert(varchar(5),getdate(),108),':','')
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6442699
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 44

Expert Comment

by:bruintje
ID: 6442708
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6442710
oops should refresh
0
 

Author Comment

by:BirdsOfFire1
ID: 6448470
Thanks for answering my call and thanks to the other MS-SQL experts answering my call.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
disk usage reporting tools 27 54
Options for Linking SQL tables to Access 2013 9 42
SQL - Ordering Supervisor Hierarchy 2 13
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question