Wednesday, February 3, 2010

Get Only Date from DateTime in T-SQL

I need to sort the records based entrydate, transferType, then amount.
So I wrote something like
SELECT entrydate
FROM mytable
ORDER BY groupvalue, transfertype, amout
The result is not correct because the entrydate column is date time column so when doing the sort, records on the same date but different time are considered different group, so the later transfertype and amount sort is not grouped correctly.
I need to sort the entrydate only by the date part. I google around and find a solution at Anatoly Lubarsky's blog on T-SQL: Get only Date from DateTime.

To get the date part, we can use:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
So my previous t-sql statement becomes:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, entrydate )) 'groupvalue'
FROM mytable
ORDER BY groupvalue, transfertype, amout
Now everything is working correctly.

1 comment :