SQL Server 2008: Get date part from datetime2

sql-server sql-server-2008 datetime2
Trent picture Trent · Dec 8, 2012 · Viewed 12.7k times · Source

I've been using this very handy method of extracting the date portion from a datetime value:

select dateadd(day, datediff(day, 0, @inDate), 0)

This basically zeroes out the time portion of the date. It's fast, and more importantly it's deterministic - you can use this expression in a persisted computed column, indexed view, etc.

However I'm struggling to come up with something that works with the datetime2 type. The problem being that SQL Server does not permission conversions from integers to the datetime2 type.

Is there an equivalent, deterministic method of stripping the time portion from a datetime2?

Answer

marc_s picture marc_s · Dec 8, 2012

Can't you just do a 

ALTER TABLE dbo.YourTable
ADD DateOnly AS CAST(YourDate AS DATE) PERSISTED

The CAST(... AS DATE) turns your datetime2 column into a "date-only", and it seems to be deterministic enough to be used in a persisted, computed column definition ...

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More