Convert mm/dd/yy hr:min:sec to yyyy-mm-dd hr:min:sec

Question

I want to convert (this is as in Syabase SQL)

'09/29/2012 08:23:00' to '2012-09-29 08:23:00'

This what i have tried but failed

select convert(varchar,convert(datetime, '09/29/2012 08:23:00', 101),123) 

Answer 1

Try 120 instead of 123.

 select convert(varchar(20),convert(datetime, '09/29/2012 08:23:00', 101),120) 

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/Convert.htm

Answer 2

Im not sure where you are getting that date format from but if it is from a table in the database what you can do is use the following to convert the date directly from the table.

Lets assume the date is in a table called MyTable and the date column is called MyDate

SELECT dateformat(MyDate, 'YYYY-MM-DD HH:MM:SS')
FROM MyTable;

You can test this out with the current datetime using the following:

SELECT dateformat(getdate(), 'YYYY-MM-DD HH:MM:SS');