Combining (Concatenating) Date and Time into a 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.

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.

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)

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);

Concatenate date and string to create datetime in SQL

Assuming 11:00 stands for 11:00:00, you can do something like this:

SELECT dtDate + CONVERT(DateTime, szTime, 108)
FROM...

See a live demo on rextester

datetime combine date & time stamp

You can read the specified columns in datetime format using parse_dates parameter or read them as objects and then convert to datetime.

Option 1:

df= pd.read_csv('C:\\Users\\desktop\\master.csv', parse_dates=[['Date', 'Time']])

Option 2:

df= pd.read_csv('C:\\Users\\desktop\\master.csv')
df['datetime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'])

Combining different date and time columns to form a datetime value

Use to_datetime with %I for parse hour in 12H format with %p for parse AM/PM. Last if need in output AM/PM is necessary convert to strings by Series.dt.strftime:

df = pd.read_csv('a.csv')

print (df)
Date Time
0 01JAN2017 12:00:00 AM

df["DateTime"] = pd.to_datetime(df["Date"] + ' ' +df["Time"], format='%d%b%Y %I:%M:%S %p')
df["DateTime_str"] = df["DateTime"].dt.strftime('%Y-%m-%d %I:%M:%S %p')
print (df)
Date Time DateTime DateTime_str
0 01JAN2017 12:00:00 AM 2017-01-01 2017-01-01 12:00:00 AM


Related Topics



Leave a reply



Submit