SQL Server Convert Varchar to Datetime

Question

I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it?

Answer 1

This web has some good examples to convert any varchar to date or datetime

Answer 2

SELECT CONVERT(Datetime, '2011-09-28 18:01:00', 120) -- to convert it to Datetime

SELECT CONVERT( VARCHAR(30), @date ,105) -- italian format [28-09-2011 18:01:00]
+ ' ' + SELECT CONVERT( VARCHAR(30), @date ,108 ) -- full date [with time/minutes/sec]

Answer 3

As has been said, datetime has no format/string representational format.

You can change the string output with some formatting.

To convert your string to a datetime:

declare @date nvarchar(25) 
set @date = '2011-09-28 18:01:00' 

-- To datetime datatype
SELECT CONVERT(datetime, @date)

Gives:

-----------------------
2011-09-28 18:01:00.000

(1 row(s) affected)

To convert that to the string you want:

-- To VARCHAR of your desired format
SELECT CONVERT(VARCHAR(10), CONVERT(datetime, @date), 105) +' '+ CONVERT(VARCHAR(8), CONVERT(datetime, @date), 108)

Gives:

-------------------
28-09-2011 18:01:00

(1 row(s) affected)

Answer 4

this website shows several formatting options.

Example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 

Answer 5

Try the below

select Convert(Varchar(50),yourcolumn,103) as Converted_Date from yourtbl

Answer 6

You could do it this way but it leaves it as a varchar

declare @s varchar(50)

set @s = '2011-09-28 18:01:00'

select convert(varchar, cast(@s as datetime), 105) + RIGHT(@s, 9)

or

select convert(varchar(20), @s, 105)