April 20, 2009

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column


Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.

Let's see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)
INSERT INTO @Data(dt)
SELECT '2008-12-05 04:00:00.000'
UNION ALL
SELECT '2008-12-10 10:00:00.000'
UNION ALL
SELECT '2009-03-01 08:00:00.000'
UNION ALL
SELECT '2009-03-02 07:15:00.000'
UNION ALL
SELECT '2009-03-10 08:50:00.000'
UNION ALL
SELECT '2008-12-31 23:00:00.000'
UNION ALL
SELECT '2009-05-01 21:10:00.000'

SELECT * FROM @Data


Now we need output like this:

Get Result Order BY Time regardless Date on DateTime column


I found very quick solution for this. You can create query as follows:


SELECT * 
FROM @Data
ORDER BY Convert(VARCHAR, dt,108)


Let me know if it helps you in any way.

1 comment: