Get only the Date part of DateTime in mssql

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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