SQL convert doesn't work

Posted on 2009-02-12
Last Modified: 2013-11-30
When I query the following:
SELECT     schdtime AS Before, CONVERT(datetime, schdtime, 120) AS After
FROM         sheet
I get:

            Before                                            After
1/29/2009 5:00:00 PM                    1/29/2009 5:00:00 PM

I'm trying to convert the time to a 24-hour format but it isn't working, any ideas?

Question by:dcmorrell
    LVL 57

    Expert Comment

    by:Raja Jegan R
    This works fine for me..
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Can you provide some more details so that we can try to identify where exactly is the problem.

    What about the datatype of the column?
    LVL 40

    Expert Comment

    Its working fine as rrjegan17 said. Post what exactly you want?
    declare @schdtime varchar(20)
    set @schdtime = '1/29/2009 5:00:00 PM'
    select @schdtime before ,convert(datetime,@schdtime,120) after
    before	                  after
    1/29/2009 5:00:00 PM	2009-01-29 17:00:00.000
    declare @schdtime datetime
    set @schdtime = '1/29/2009 5:00:00 PM'
    select @schdtime before ,convert(datetime,@schdtime,120) after
    before	                   after
    2009-01-29 17:00:00.000	2009-01-29 17:00:00.000

    Open in new window

    LVL 142

    Accepted Solution

    you wnat:
    SELECT     schdtime AS Before, CONVERT(varchar(20), schdtime, 120) AS After
    FROM         sheet

    Open in new window

    LVL 29

    Expert Comment

    by:Göran Andersson
    A datetime value doesn't have a format at all, it only gets a format when it's converted into text. So, to get the datetime value in a specific format you have to convert it to a varchar, not a datetime.

    To get a format like  2009-01-29 17:00:00 you use a varchar(19):

    select schdtime as Before, convert(varchar(19), schdtime, 120) as After
    from sheet

    If you want it without the seconds, use a varchar(16) instead.
    LVL 22

    Expert Comment

    CONVERT has no control over how your client application displays datetime values. That's purely a client-side feature.

    The purpose of CONVERT is to permit conversion between types. You could convert the datetime value to a string but in most cases it would be better to control formatting in your presentation tier.

    Author Closing Comment

    Thank you.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now