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?

Solution 1

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]

Solution 2

Like this

DECLARE @date DATETIME
SET @date = '2011-09-28 18:01:00'
select convert(varchar, @date,105) + ' ' + convert(varchar, @date,108)

Solution 3

this website shows several formatting options.

Example:

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

Solution 4

You can have all the different styles to datetime conversion :

https://www.w3schools.com/sql/func_sqlserver_convert.asp

This has range of values :-

CONVERT(data_type(length),expression,style)

For style values,
Choose anyone you need like I needed 106.

Solution 5

Try the below

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

View more solutions

Comments

[MatBailie]

I want to convert it to datetime changing to this format 28-09-2011 18:01:00. - Where do you use the new format?

[MatBailie]

Except that 105 doesn't include a time part. And the OP is starting with a string, not a datetime. This is helpful, but not what I'd consider a complete answer.

[daniloquio]

This works but there is no need for four converts. Look at Diego's answer SELECT CONVERT(VARCHAR(30), GETDATE(), 105)

[Diego]

thanks for the comments lads. I actually just wanted to point him in the right direction, that's why I wrote "example"

[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz]

Just remember the datetime variable has no format. You must convert it to a varchar if you want it to display in a certain format.

[daniloquio]

Yes, Diego's answer needs to change varchar(10) to (30). Then it will work.

[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz]

@daniloquio, that is just not true

[daniloquio]

You are right, my mistake. Is SELECT CONVERT( varchar(30), '2011-09-28 18:01:00' ,105) what results in '2011-09-28 18:01:00'

[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz]

No SELECT CONVERT( varchar(30), '2011-09-28 18:01:00' ,105) does nothing, it converts a string to a string, you need to pass it a datetime object.

[daniloquio]

Did you ran it in a query window? I did it and pasted the result in the comment.

[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz]

@daniloquio yes i did but '2011-09-28 18:01:00' is a varchar, so you are converting a varchar to a varchar, that is why it looks correct

[daniloquio]

Oh crap, I just realized, you are totally right. (+1-1 = 0) for Diego, +1 for you. I don't know how to say it in english... la iba re cagando, I'm sorry.

[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz]

It's okay, I was just pointing it out so no one would be confused.

[Zyku]

datetime has no format , but you have to specify it how to reproduce the text (where id the day / month ...)

[Matt]

Your answer seems to simply repeat the information given in an existing answer. Because of this, I've deleted your answer. If you wish to add new information to your answer, please edit you post then flag it for un-deletion.