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 AMAnd 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
