How to Combine Date from One Field With Time from Another Field - Ms SQL Server

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

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.

SQL Combine Date and Time columns into single DateTime column

Why the subquery? Just use the expression directly. (Just for the record: Maybe the expression can be optimized/simplyfied as well, I didn't look into this.)

UPDATE dbo.orders 
       SET orderdate = cast(dateadd(DAY, 0, datediff(DAY, 0, date)) AS datetime)
                       + cast(dateadd(DAY, 0 - datediff(DAY, 0, time), time) AS datetime);

Combining (concatenating) date and time into a datetime

Assuming the underlying data types are date/time/datetime types:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
  + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
  FROM dbo.whatever;

This will convert CollectionDate and CollectionTime to char sequences, combine them, and then convert them to a datetime.

The parameters to CONVERT are data_type, expression and the optional style (see syntax documentation).

The date and time style value 112 converts to an ISO yyyymmdd format. The style value 108 converts to hh:mi:ss format. Evidently both are 8 characters long which is why the data_type is CHAR(8) for both.

The resulting combined char sequence is in format yyyymmdd hh:mi:ss and then converted to a datetime.

Combine Date from one field with time from another field - SSIS

You need two steps to achieve your goal.

1) First merge both Dates and Times into a single row. I am guessing you have a key to tie the two up, so use this inside a merge join transformation (you will need to sort by this column prior to entering the merge) to create a single row e.g.

2) Convert the two columns into one inside a derived column transformation with the following casts (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,23)Dates,1,11) + SUBSTRING((DT_WSTR,23)Times,12,8))

This should provide you with a new column of datetime to insert into your database e.g.

Concatenate Date and Time as DATETIME in SQL Server

Don't concat(). Just add but they both need to be datetime:

select cast(startdate as datetime) + cast(starttime as datetime)

How to combine date and time field in sql?

SELECT FORMAT(CAST(date AS date), 'yyyyMMdd') + FORMAT(CAST(time AS time), 'hhmmss')

should work...

How to combine date from one column and time from another

Here you go:

SELECT TO_DATE(TO_CHAR(t.fromdate,'dd-mm-yyyy') ||
               ' ' ||
               TO_CHAR(t.fromtime,'hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') as full_date_col 
FROM YourTable t

Concatenate Date and Time Variable using SQL Query

In SQL-Server 2008, you can add the values this way:

select cast(dt as date) + cast(tm as datetime)
from yourtable

See SQL Fiddle with Demo

The above no longer works, here is an updated version that will work in SQL Server. This can also be edited to include additional precision:

SELECT dateadd(day, datediff(day,'19000101',dt), CAST(tm AS DATETIME))
from yourtable

See Demo.

---

Related Topics