date into datetime conversion

Posted on 2011-10-06
Last Modified: 2012-08-14
I would like to convert the date into datetime format in the sql below.  Currently my date is showing like this 09-12-2011 and would like to convert it into something like this 09/12/2011 02:10:12AM something like this.  My Date column is the post_date.  Many thanks

SELECT distinct [post_date]
      ,convert(varchar(MAX),[post_content]) [post_content]
      ,convert(varchar(64),post_title) post_title
  FROM MyTable
where category_id NOT IN (28,103)
order by post_date, convert(varchar(64),post_title) desc

Open in new window

Question by:karinos57
    LVL 18

    Accepted Solution

    SELECT distinct CONVERT(datetime, [post_date], 109) AS post_date...

    Author Closing Comment

    LVL 59

    Expert Comment

    by:Kevin Cross
    I am not sure 109 is the proper format code. In addition, converting to a DATETIME will result in it being displayed by the default format based on language setting of server, which appears to be MM-DD-YYYY.

    Here is a reference on the format codes:
    It appears the server is set for Italian, 105.

    So the code would be like the following if you want it to look like US, 101, MM/DD/YYYY ...:
    CONVERT(VARCHAR, [post_date], 101)

    LVL 59

    Expert Comment

    by:Kevin Cross
    109 is "mon dd yyyy hh:mi:ss:mmmAM (or PM)" -- with respect, there is no way that comment "works" unless the question's examples are incorrectly stated.
    LVL 18

    Expert Comment

    No disrespect taken, hopefully it truly is working.
    LVL 59

    Expert Comment

    by:Kevin Cross
    @UnifiedIS: I am glad you did not take that anyway. I almost posted a direct comment to make my comment a little more clear. My initial comment here was typed before/while I guess the question was being PAQ'd. I was hoping my shocked response after seeing question closed after refresh did not get taken the wrong way. As you said, if it is truly working that is all that counts.

    @karinos57: hopefully, you can post back to clarify for future readers. My confusion is a DATETIME does not carry a specific format. Format is based on language/format settings of server or connection/session. So if [post_date] is returning a default format you do not like, converting to DATETIME does not seem like it would fix it. It would convert based on a format, but because it is a DATETIME, it will store numerically and then display based on same default you set out to fix in the first place, right?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Title # Comments Views Activity
    Help with SQL 9 61
    Parsing the XML data to SQL Server 4 35
    Oracle PL/SQL syntax 4 29
    Import csv files to MS SQL 5 34
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this article I will describe the Detach & Attach 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.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now