[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Format output of time from a datetime field

Posted on 2008-11-05
5
Medium Priority
?
541 Views
Last Modified: 2012-05-05
I'm extracting data from sql server and need to output the data in a specific order and format.  The field I'm having trouble with is defined in sql server as a datetime field (8).  My extract requires the time to be formatted as a numeric 4 chars, as in military time with leading zeros, so 8:00 should appear as 0800   Here is my current statement:

Convert(varchar(10),dbo.calendar.start_time,108) as BegTime
which returns 08:00:00

I can't figure out if there is a style parameter that will give me what I need.
0
Comment
Question by:zknoll
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 22887532
i am not sure aobut style param
but this may help
select convert(varchar(2), DATEPART(hour, getdate())) + convert(varchar(2), DATEPART(minute, getdate()))
0
 
LVL 3

Accepted Solution

by:
dhanushkad earned 2000 total points
ID: 22887620

SELECT REPLACE(SUBSTRING(CONVERT(CHAR(8),GETDATE(),8) , 1,5),':','')
0
 
LVL 13

Expert Comment

by:sm394
ID: 22887647
here format options in detail
http://doc.ddart.net/mssql/sql70/ca-co_1.htm
0
 

Author Comment

by:zknoll
ID: 22887920
convert(varchar(2), DATEPART(hour, getdate())) + convert(varchar(2), DATEPART(minute, getdate()))

This works for a time like 10:30.  The output is 1030 but if the time is 8:00:00 the output is 80 so I'm missing the leading 0 before the 8 and the second 0 for the minute.
0
 

Author Closing Comment

by:zknoll
ID: 31513572
It would have easier to understand if you referenced by field in the solution.  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

829 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