sql - Date without the time

Question

I'm working with SQL Server 2005.

I have a column called purchase_time of type datetime. How do I select this column with the time part - just the date.

Thanks,

Barry

EDIT: Would it be safe to get the datetime and split it via Python on the first space, or is this format locale dependant?

Answer 1

In versions < 2008 (which, based on other comments to some of the answers, I believe you are running), the most efficient way is to keep it as a datetime type and use date math to avoid string conversions.

SELECT DATEADD(DAY, DATEDIFF(DAY, '20000101', purchase_time), '20000101') 
  FROM dbo.table;

EDIT

If you want the date only for display purposes, not for calculations or grouping, that is probably best handled at the client. You can do it in SQL simply by saying:

SELECT dt = CONVERT(CHAR(10), purchase_time, 120)
  FROM dbo.table;