CODEWITHSUNDEEP
sqlsql-server-2005
Baz
Baz

Reputation: 13125

Date without the time

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?

Upvotes: 1

Views: 165

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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;

Upvotes: 4

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

In SQL Server 2008 you can use the newly added date type:

select convert(date, purchase_time) from TableName

Update:

In versions prior to SQL 2008, I used the following solution for this problem:

select convert(datetime, convert(int, convert(float, purchase_time)))
from TableName

Upvotes: 1

Related Questions