Solved

Converting Smalldatetime to Military Time

Posted on 2001-08-30
6
1,271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how the fundamental information of how to create a table.

691 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