CODEWITHSUNDEEP
sql-servert-sqldatetimeselectvarchar
Roxzurafa
Roxzurafa

Reputation: 13

Most Efficient Way to Display the Date Only in Datetime Column and Then Convert it Into Varchar after

I want to display a column with a data type of datetime as varchar. But I don't need the time to be displayed. This is what I'm currently using

SELECT 
    CONVERT(varchar(50), CONVERT(date, [Date_Created])) AS 'Date_Created'
FROM 
    Accounts_Table

Is there are more efficient or rather less CPU extensive in doing this?

Upvotes: 0

Views: 833

Answers (4)

prem
prem

Reputation: 323

SELECT CONVERT(varchar(10),LEFT('2021-25-01 06:13:18.430',10)) As 'Date_Created'

Upvotes: 0

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

try casting the datetime as a date then cast the date as a varchar string

  select cast(cast(getDate() as date) as varchar) string_date

Upvotes: 0

gvee
gvee

Reputation: 17161

Whilst Convert() with a style has been the normal approach for a long time, I believe that we now have a better option: Format() https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

SELECT Format(Date_Created, 'yyyy-MM-dd') AS Date_Created
FROM   Accounts_Table
;

I think that this approach is better as the intention is easier to understand (yyyy-MM-dd is clearly intended, where 120 is something that has to be looked up).

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You may try:

SELECT 
    CONVERT(varchar(10), [Date_Created], 120) AS [Date_Created]
FROM Accounts_Table;

The format mask for 120 is yyyy-mm-dd hh:mi:ss, and by using varchar(10) in the conversion, we only retain the first 10 characters, which is the date portion only. My approach is still calling CONVERT, but at least it avoids the nested calls which you were making.

Upvotes: 5

Related Questions