[sql] SQL Server Convert Varchar to Datetime

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

The answer is


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)

this website shows several formatting options.

Example:

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to formatting

How to add empty spaces into MD markdown readme on GitHub? VBA: Convert Text to Number How to change indentation in Visual Studio Code? How do you change the formatting options in Visual Studio Code? (Excel) Conditional Formatting based on Adjacent Cell Value 80-characters / right margin line in Sublime Text 3 Format certain floating dataframe columns into percentage in pandas Format JavaScript date as yyyy-mm-dd AngularJS format JSON string output converting multiple columns from character to numeric format in r

Examples related to varchar

SQL Server date format yyyymmdd What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? Difference between VARCHAR and TEXT in MySQL PostgreSQL: ERROR: operator does not exist: integer = character varying Can I use VARCHAR as the PRIMARY KEY? Is the LIKE operator case-sensitive with MSSQL Server? How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric What is the MySQL VARCHAR max size? SQL Server Convert Varchar to Datetime