Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

How to convert DATETIME value to VARCHAR value in SQL Server

  • How-Tos FAQs
  • December 17, 2018

We can convert the DATETIME value to VARCHAR value in SQL server using the CONVERT function. Convert function has three arguments.

CONVERT (target_type, expression, style)  

  • target_type: use VARCHAR in the argument
  • expression: put DATETIME with needs to be converted
  • style: Date can be converted to many different style like dd/mm/yyyy, yyyy/mm/dd, yyymmdd etc

Let us convert a DATETIME value into VARCHAR with different date styles.

DECLARE @datetime DATETIME = '2020-10-23 11:21:44.887'

-- Convert date into mm/dd/yy
SELECT CONVERT(VARCHAR, @datetime, 1) [Date]

-- Output
Date
---------
10/23/20

-- Convert date into dd/mm/yy
SELECT CONVERT(VARCHAR, @datetime, 3) [Date]

-- Output
Date
---------
23/10/20

-- Convert date into mmm dd,yy
SELECT CONVERT(VARCHAR, @datetime, 7) [Date]

-- Output
Date
-----------
Oct 23, 20

-- Convert date into mm/dd/yyyy
SELECT CONVERT(VARCHAR, @datetime, 101) [Date]

-- Output
Date
---------
10/23/2020

-- Convert only time part hh:mm:ss
SELECT CONVERT(VARCHAR, @datetime, 8) [Date]

-- Output
Date
---------
11:21:44

-- Convert only time part hh:mm:ss:minsec
SELECT CONVERT(VARCHAR, @datetime, 14) [Date]

-- Output
Date
-------------
11:21:44:887

-- Convert both date and time into yyyy-mm-dd hh:mm:ss
SELECT CONVERT(VARCHAR, @datetime, 20) [Date]

-- Output
Date
---------------------
2020-10-23 11:21:44

-- Convert both date and time into mm/dd/yy hh:mm:ss am/pm
SELECT CONVERT(VARCHAR, @datetime, 22) [Date]

-- Output
Date
---------------------
10/23/20 11:21:44 AM

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

And that’s how to convert DATETIME value to VARCHAR value in SQL Server!


Up Next:

Read How to UPDATE a table by joining multiple tables in SQL

More Resources We Think You Might Like

SQL FAQ Feat

How to find the duplicate data in a table in SQL?

Let us set up a ‘customers’ table with their email addresses: CREATE TABLE customers (     custom...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

In SQL, how to limit the number of rows after o...

We can perform ‘Like’ operations in MongoDB using regular expressions. In fact, regular expressio...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018