[sql-server] Date format in dd/MM/yyyy hh:mm:ss

I need to convert datetime from 2012-07-29 10:53:33.010 to 29/07/2012 10:53:33.

I tried using

select CONVERT(varchar(20), GETDATE(), 131)

but its showing date according to Hijri calendar

11/09/1433 10:53:33:

Please help?

CREATE FUNCTION DBO.ConvertDateToVarchar
(
@DATE DATETIME
)

RETURNS VARCHAR(24) 
BEGIN
RETURN (SELECT CONVERT(VARCHAR(19),@DATE, 121))
END

SELECT CONVERT(CHAR(10),GETDATE(),103) + ' ' + RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)

You could combine 2 formats:

3   dd/mm/yy   (British/French)
8   hh:mm:ss

according to CONVERT() function, and using + operator:

SELECT CONVERT(varchar(10),GETDATE(),3) + ' ' + CONVERT(varchar(10),GETDATE(),8)

SELECT FORMAT(your_column_name,'dd/MM/yyyy hh:mm:ss') FROM your_table_name

Example-

SELECT FORMAT(GETDATE(),'dd/MM/yyyy hh:mm:ss')

The chapter on CAST and CONVERT on MSDN Books Online, you've missed the right answer by one line.... you can use style no. 121 (ODBC canonical (with milliseconds)) to get the result you're looking for:

SELECT CONVERT(VARCHAR(30), GETDATE(), 121)

This gives me the output of:

2012-04-14 21:44:03.793

Update: based on your updated question - of course this won't work - you're converting a string (this: '4/14/2012 2:44:01 PM' is just a string - it's NOT a datetime!) to a string......

You need to first convert the string you have to a DATETIME and THEN convert it back to a string!

Try this:

SELECT CONVERT(VARCHAR(30), CAST('4/14/2012 2:44:01 PM' AS DATETIME), 121) 

Now you should get:

2012-04-14 14:44:01.000

All zeroes for the milliseconds, obviously, since your original values didn't include any ....

This will be varchar but should format as you need.

RIGHT('0' + LTRIM(DAY(d)), 2) + '/'
+ RIGHT('0' + LTRIM(MONTH(d)), 2) + '/'
+ LTRIM(YEAR(d)) + ' '
+ RIGHT('0' + LTRIM(DATEPART(HOUR, d)), 2) + ':'
+ RIGHT('0' + LTRIM(DATEPART(MINUTE, d)), 2) + ':'
+ RIGHT('0' + LTRIM(DATEPART(SECOND, d)), 2)

Where d is your datetime field or variable.