CODEWITHSUNDEEP
sqlsql-servert-sqldatetimesql-server-2012
Kcird
Kcird

Reputation: 97

SQL convert datetime to varchar

I want to convert DATETIME to VARCHAR (month/day/year) like this:

10/09/2018 12:00:00.000

I tried using 

Convert(VARCHAR(MAX),[Date & Time Added]),121)

but it returns 

yyyy-mm-dd hh:mi:ss.mmm

I need the / format with time, I am Using SQL Server 2012.

Upvotes: 1

Views: 6790

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272036

You can use the FORMAT function:

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy HH:mm:ss.fff')
-- 10/09/2018 00:58:52.557

Complete list of format specifiers is actually available in the .NET documentation.

If FORMAT function is unavailable you could simply format in a known format and use string functions to re-arrange the year, month, day and time parts. For example:

SELECT SUBSTRING(DateStr, 6, 2) + '/' + SUBSTRING(DateStr, 9, 2) + '/' + SUBSTRING(DateStr, 1, 4) + ' ' + SUBSTRING(DateStr, 12, 12)
FROM (
    SELECT CONVERT(VARCHAR(23), GETDATE(), 126) -- ISO8601 / yyyy-mm-ddThh:mi:ss.mmm
) AS CA(DateStr)
-- 10/09/2018 01:12:50.833

SELECT CONVERT(VARCHAR(10), Date, 101) + ' ' + CONVERT(VARCHAR(12), Date, 114)
FROM (
    SELECT GETDATE()
) AS CA(Date)
-- 10/09/2018 01:19:38:463

Upvotes: 6

Diado
Diado

Reputation: 2257

This is something that would usually be done in the presentation layer, but if you need to do it in the data layer you can use FORMAT (documentation here)

Syntax is: FORMAT ( value, format [, culture ] )

So in your case (edited to add AM/PM designation per your comments): FORMAT([Date & Time Added], 'dd/MM/yyy hh:mm:ss.fff tt')

Custom date format string options are detailed here

Upvotes: 3

Related Questions