How to combine date from one field with time from another field – MS SQL Server

by

You can simply add the two.

  • if the Time part of your Date column is always zero
  • and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

It works like this due to the way the date is stored as two 4-byte
Integers with the left 4-bytes being the date and the right
4-bytes being the time. Its like doing $0001 0000 + $0000 0001 =
$0001 0001

Edit regarding new SQL Server 2008 types

Date and Time are types introduced in SQL Server 2008. If you insist on adding, you can use Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to Martin Smith)

Have a look at How to combine date and time to datetime2 in SQL Server? to prevent loss of precision using SQL Server 2008 and up.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. DateTime2 vs DateTime in SQL Server
  5. SQL query to select dates between two dates
  6. How can I select the first day of a month in SQL?
  7. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  8. How to convert DateTime to VarChar
  9. Get day of week in SQL Server 2005/2008
  10. T-SQL: Round to nearest 15 minute interval
  11. SQL Server Convert Varchar to Datetime
  12. How Can I use “Date” Datatype in sql server?
  13. How to determine the number of days in a month in SQL Server?
  14. Calculate business hours between two dates
  15. SQL Server convert string to datetime
  16. How do I query for all dates greater than a certain date in SQL Server?
  17. A way to extract from a DateTime value data without seconds
  18. How can I compare time in SQL Server?
  19. Difference of two date time in sql server
  20. Custom Date/Time formatting in SQL Server
  21. How to compare datetime with only date in SQL Server
  22. Getting Dates between a range of dates
  23. How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  24. What is the internal representation of datetime in sql server?
  25. SQL SERVER DATETIME FORMAT
  26. How to calculate percentage with a SQL statement
  27. Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
  28. Log record changes in SQL server in an audit table
  29. How can I backup a remote SQL Server database to a local drive?
  30. How to subtract 30 days from the current date using SQL Server

Leave a Comment