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.

Answer 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;

Answer 2

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

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

> 08/01/2013

An SQLfiddle.