CODEWITHSUNDEEP
sql-servert-sql
kneidels
kneidels

Reputation: 924

sql CONVERT to datetime does not change printed date format

I have a column in a table updatedDate - which is a datetime data type.

Data sample:

2017-10-15 18:08:22.000
2017-10-15 18:07:44.000
2017-10-15 18:07:17.000
2017-10-15 18:07:10.000
2017-10-14 18:00:54.000
2017-10-13 17:59:23.000
2017-10-13 17:59:13.000

I would like to display a list of DISTINCT dates, in the format of dd/mm/yyyy, but for the life of me... I can't get it. I would think it should be:

 SELECT DISTINCT convert(datetime,updatedDate,103)
 FROM [tblStudentCourses] 
 ORDER BY updatedDate DESC

But it does not actually convert to the 103 format... it just gives it to me as the full date and time format as originally, without any CONVERT.

What I want to get would be:

15/10/2017
14/10/2017
13/10/2017

What am i doing wrong? Thanks!

Upvotes: 0

Views: 1073

Answers (2)

Aswani Madhavan
Aswani Madhavan

Reputation: 816

SELECT DISTINCT convert(VARCHAR,updatedDate,103)
FROM [tblStudentCourses] 
ORDER BY updatedDate DESC

You should use convert to varchar for formating to various date formats

Upvotes: -1

ZLK
ZLK

Reputation: 2884

To display the date in DD/MM/YYYY format, you can use CONVERT(VARCHAR(10), DateColumn, 103).

To maintain the proper ordering, you can wrap it all in a subquery. For example:

SELECT DisplayDate
FROM (
    SELECT DISTINCT 
        DisplayDate = CONVERT(VARCHAR(10), CAST(UpdatedDate AS DATE), 103),
        ActualDate = CAST(UpdatedDate AS DATE)
    FROM [tblStudentCourses] 
) AS T
ORDER BY ActualDate;

Note: Cast the date column to date if it's datetime like your sample data.

Upvotes: 2

Related Questions