Truncating DateTime In SQL Server

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:

  • casting;
  • manipulating with string conversion;
  • working on total number of years/months/days/hours in DateTime value.

Casting works fine for truncating to 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).

Zero means the January 1, 1900.