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?
This question is related to
sql
sql-server
datetime
formatting
varchar
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)
Like this
DECLARE @date DATETIME
SET @date = '2011-09-28 18:01:00'
select convert(varchar, @date,105) + ' ' + convert(varchar, @date,108)
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) + ' ' + CONVERT(VARCHAR(10), GETDATE(), 108)
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.
Try the below
select Convert(Varchar(50),yourcolumn,103) as Converted_Date from yourtbl
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)
Source: Stackoverflow.com