Get only the Date part of DateTime in mssql

How can I get only the Date part of a DateTime ? I'm searching for something like year() function but for the whole date.

From stackoverflow

• The solution you want is the one proposed here:

  http://stackoverflow.com/questions/542780/get-just-the-date-from-grouping-in-select-from-datetime-column-in-sql-server

  Basically, you do this:

  cast(floor(cast(@dateVariable as float)) as datetime)
  

  There is a function definition in the link which will allow you to consolidate the functionality and call it anywhere (instead of having to remember it) if you wish.

• Another nifty way is:

  DATEADD(dd, 0, DATEDIFF(dd, 0, [yourdate]))

  Which gets the number of days from DAY 0 to YourDate and the adds it to DAY 0 to set the baseline again. This method (or "derivatives" hereof) can be used for a bunch of other date manipulation.

  Edit: True, Joe, it does not add it to DAY 0, it adds 0 (days) to the number of days which basically just converts it back to a datetime.

  dotjoe : Dateadd(interval, number, date) ... so essentially you are adding 0 to this... cast(datediff(dd, 0, [yourdate]) as datetime)

• This may not be as slick as a one-liner, but I use it to perform date manipulation mainly for reports:

  DECLARE @Date datetime
  SET @Date = GETDATE()
  
  -- Set all time components to zero
  SET @Date = DATEADD(ms, -DATEPART(ms, @Date), @Date) -- milliseconds = 0
  SET @Date = DATEADD(ss, -DATEPART(ss, @Date), @Date) -- seconds = 0
  SET @Date = DATEADD(mi, -DATEPART(mi, @Date), @Date) -- minutes = 0
  SET @Date = DATEADD(hh, -DATEPART(hh, @Date), @Date) -- hours = 0
  
  -- Extra manipulation for month and year
  SET @Date = DATEADD(dd, -DATEPART(dd, @Date) + 1, @Date) -- day = 1
  SET @Date = DATEADD(mm, -DATEPART(mm, @Date) + 1, @Date) -- month = 1
  

  I use this to get hourly, daily, monthly, and yearly dates used for reporting and performance indicators, etc.

• In case any one is interested in achieving the same via jpa/hibernate, this "article" might help you.