Sometimes we need to get only date from a datetime value, or truncate time to an hour or minute in SQL Server. There are some ways to do this:
- manipulating with string conversion;
- working on total number of years/months/days/hours in DateTime value.
Casting works fine for truncating to date.
-- truncate to day
SELECT CAST(@now AS date);
You can convert datetime value to string (cast to nvarchar type), get a substring, and convert back to datetime. But it’s not efficient and can depend on string format (lond or short date format) or locale.
Another way is to calculate the number of years/months, etc. from a special date (DATEDIFF), then add this number of years/months to that special date (DATEADD).
-- truncate to month
SELECT DATEADD(month, DATEDIFF(month, 0, @now), 0);
-- truncate to hour
SELECT DATEADD(hour, DATEDIFF(hour, 0, @now), 0);
-- truncate to minute
SELECT DATEADD(minute, DATEDIFF(minute, 0, @now), 0);
Zero means the January 1, 1900.