9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Trent
  • 1,089
  • 3
  • 12
  • 24
  • 1
    How about `SELECT CONVERT(DATE, @inDate)`? Or `SELECT CONVERT(DATETIME2, CONVERT(DATE, @inDate))` if you need to return a `DATETIME2`. – Tim S Dec 08 '12 at 12:17

1 Answers1

14

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 ...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    I guess so! cast with datetime is not deterministic, but apparently it's okay to be used with date and datetime2... – Trent Dec 08 '12 at 12:41