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 yourDate
column is always zero - and the
Date part
of yourTime
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 thedate
and the right
4-bytes being thetime
. 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
Mysql Split Comma Separated String into Temp Table
How to Limit Results of a Left Join
How to Get the Last 12 Months from the Current Date
How to in a Query Calculate the Sum of the Union
Laravel Update Multiple Records At Once
Deleting Rows from Parent and Child Tables
Update Only Time from My Datetime Field in SQL
How to Convert a Timestamp (Date Format) to Bigint in SQL
How to Select the Top 3 Salaries of the Department
Group MySQL Query by 15 Min Intervals
Sql Select Count for Multiple Columns in a Single Query
Sql 0 Results for 'Not In' and 'In' When Row Does Exist
How to Convert Yyyy-Mm-Dd Hh:Mm:Ss to Mm-Dd-Yyyy Hh:Mm:Ss in SQL Server
Select Different Values from Same Column in a Table and Display It Under Different Columns
How to Select True/False Based on Column Value
Postgresql Error: Syntax Error At or Near "Varchar"
Sqlstate[Hy000] [2002] Php_Network_Getaddresses: Getaddrinfo Failed: Name or Service Not Known