• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

date into datetime conversion

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

  • 3
  • 2
1 Solution
SELECT distinct CONVERT(datetime, [post_date], 109) AS post_date...
karinos57Author Commented:
Kevin CrossChief Technology OfficerCommented:
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)

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Kevin CrossChief Technology OfficerCommented:
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.
No disrespect taken, hopefully it truly is working.
Kevin CrossChief Technology OfficerCommented:
@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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now