• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • 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
      ,[post_name]
  FROM MyTable
where category_id NOT IN (28,103)
order by post_date, convert(varchar(64),post_title) desc

Open in new window

0
karinos57
Asked:
karinos57
  • 3
  • 2
1 Solution
 
UnifiedISCommented:
SELECT distinct CONVERT(datetime, [post_date], 109) AS post_date...
0
 
karinos57Author Commented:
tx
0
 
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:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
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)

Kevin
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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.
0
 
UnifiedISCommented:
No disrespect taken, hopefully it truly is working.
0
 
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?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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