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) descASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
@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?
@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?
ASKER