Format output of time from a datetime field

Posted on 2008-11-05
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.
Question by:zknoll
    LVL 13

    Expert Comment

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

    Accepted Solution


    LVL 13

    Expert Comment

    here format options in detail

    Author Comment

    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.

    Author Closing Comment

    It would have easier to understand if you referenced by field in the solution.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now