CODEWITHSUNDEEP
sql-serversql-server-2008
user1500633
user1500633

Reputation:

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?

Upvotes: 21

Views: 243877

Answers (9)

Dani Chaile
Dani Chaile

Reputation: 1

Combination of 2 formats:

select CONVERT(VARCHAR, GETDATE(), 103) + ' '  + convert(VARCHAR, GETDATE(), 24)

Upvotes: 0

Ujjwal Tyagi
Ujjwal Tyagi

Reputation: 1

select DATE_FORMAT(NOW(),'%d/%m/%Y %h:%m:%s') 
  from dual

Try this wherever required, I have used this in JpaRepository in SpringBoot Project.

Upvotes: 0

Siddhartha Goswami
Siddhartha Goswami

Reputation: 511

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')

Upvotes: 44

Vivek Parashar
Vivek Parashar

Reputation: 9

CREATE FUNCTION DBO.ConvertDateToVarchar
(
@DATE DATETIME
)

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

Upvotes: 0

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3716

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 ....

Upvotes: 0

Hana'
Hana'

Reputation: 1

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

Upvotes: 0

HichemSeeSharp
HichemSeeSharp

Reputation: 3318

This can be done as follows : 

select CONVERT(VARCHAR(10), GETDATE(), 103) + ' '  + convert(VARCHAR(8), GETDATE(), 14)

Hope it helps

Upvotes: 35

Serge S.
Serge S.

Reputation: 4915

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)

Upvotes: 3

pyrospade
pyrospade

Reputation: 8078

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.

Upvotes: -1

Related Questions