Get only date from datetime sql without converting to varchar so that I can sort it in excel

Question

Currently sql returns the date as 2013-07-01 00:00:00.000. I want only the date part in mm/dd/yyyy format so that when I export it to excel I can still sort based on ascending or descending order. I tried varchar but it doesnt get sorted in excel.

Solution 1

You can convert to DATE, but Excel might display those as numbers, not sure.

SELECT CONVERT(DATE, col) FROM dbo.table;

Otherwise you can use a specific style, e.g.

-- yyyy-mm-dd - standard, unambiguous format
SELECT CONVERT(CHAR(10), col, 120) FROM dbo.table;

Or

-- mm/dd/yyyy - ambiguous, regional format
SELECT CONVERT(CHAR(10), col, 101) FROM dbo.table;

Solution 2

To format as mm/dd/yyyy, convert to VARCHAR using format 101;

SELECT CONVERT(VARCHAR, GETDATE(), 101);

> 08/01/2013

An SQLfiddle.

Solution 3

Try using CAST to Date:

SELECT Cast('2013-07-01 00:00:00.000' AS DATE) 

Comments

[Adir D]

@user1733271 yes, is your comment just a confirmation, or is this a problem?

[user1733271]

If I use CHAR, it wont let me sort based on date in excel

[Adir D]

So in Excel you highlight the column and tell it that this is a date.

[user1733271]

I want it in mm/dd/yyyy form

[Adir D]

And you think mm/dd/yyyy is magically going to sort by date if you don't also tell Excel this is a date? With mm/dd/yyyy it is likely still going to sort as a string, does it make sense to have Jan 2012, then Jan 2013, then Feb 2012, then Feb 2013?