Converting data to DATETIME datatype

Question

I am trying to merge a date field and a time field into a DATETIME  The date field source is a datetime and the time field source

is a NVARCHAR. I have tried to concatenatiing them as a string and convert them back into datetime  but I get an out of range error.

The source date format is yyyy-mm-dd hh:mm:ss:nnn the source time is formatted as hh:mm:ss.

Answer 1

You can either string the date data and the time data together and re-convert the string to date-time data type or convert the time to date-time and add in the number of days that the date is from date-zero.  For instance:

select
  dateadd(day, datediff(day, date_Column, 0), convert(datetime, time_Column) as dateTime_One_Way
from yourTable

alternatively:

select
  convert(datetime, convert(varchar(10), date_Column), 112)
                    + time_Column)
from yourTable

Answer 2

Try

select CONVERT(datetime,substring(the_column,1,19),120)

from tab1;

---

Many Thanks & Best Regards, Hua Min

Answer 3

I tried the first option using the adventure works database

select
dateadd(day,datediff(day,DueDate,DueDate), DueDate),convert(datetime, '01:00:00') as dateTime_One_Way from Purchasing.PurchaseOrderDetail 

and returned  1900-01-01 01:00:00.000.

I dont know what is causing thid

Answer 4

Can you try my way?

---

Many Thanks & Best Regards, Hua Min

Answer 5

I tried the first option using the adventure works database

select
dateadd(day,datediff(day,DueDate,DueDate), DueDate),convert(datetime, '01:00:00') as dateTime_One_Way from Purchasing.PurchaseOrderDetail 

and returned  1900-01-01 01:00:00.000.

I dont know what is causing thid

This should be:

select
  dateadd(day,datediff(day,DueDate,0), 
    convert(datetime, '01:00:00') as dateTime_One_Way 
from Purchasing.PurchaseOrderDetail 

( I think. )

Answer 6

I test Kent's query statement and I think the statement should be modified as,

select

dateadd(day,datediff(day,0,DueDate),

convert(datetime, '01:00:00')) as dateTime_One_Way

from

Purchasing.PurchaseOrderDetail

Answer 7

I test Kent's query statement and I think the statement should be modified as,

select

dateadd(day,datediff(day,0,DueDate),

convert(datetime, '01:00:00')) as dateTime_One_Way

from

Purchasing.PurchaseOrderDetail

Yes, think that I had it set up to return the wrong sign of the difference; sorry about that. Thank you for picking me up, Catherine.
:)

Answer 8

Try this

DECLARE @date		NVARCHAR(20)
DECLARE @time		NVARCHAR(20)
DECLARE @mergeDateTime	DATETIME

SET @date = '2012-08-21 08:42:50.643' --yyyy-mm-dd hh:mm:ss:nnn 
SET @time = '11:42:50' --hh:mm:ss

SET @mergeDateTime = CONVERT(DATETIME,(CONVERT(VARCHAR(10),GETDATE(),126)+' '+ @time))
SELECT @mergeDateTime

Answer 9

Try This one...

DECLARE @FDATE DATE;
DECLARE @TIME VARCHAR(8);

SET @FDATE = '08-12-2011';
SET @TIME = '01:00:00';

SELECT CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(VARCHAR,@FDATE)+' '+@TIME))

---

Please mark as answer or vote if it helps you...

Answer 10

In every case the time data seems to be causing the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Answer 11

Please try following

SELECT CONVERT(DATETIME,CONVERT(VARCHAR,date_Column,106) + ' ' + time_Column);

Regards

amchtwe

Answer 12

-- --------------------------------------------------------
--  When I run Catherine's query, the results look correct
-- --------------------------------------------------------
select
  dueDate,
  dateadd(day,datediff(day,0,DueDate), 
  convert(datetime, '01:00:00')) as dateTime_One_Way 
from Purchasing.PurchaseOrderDetail;
/* -------- Output: --------
dueDate                 dateTime_One_Way
----------------------- -----------------------
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
...
*/

This also appears to work correctly:

select
  dueDate,
  convert(datetime, convert(varchar(10), dueDate, 112) + ' '
                    + '01:00:00'
  ) as new_dateTime
from Purchasing.PurchaseOrderDetail;
/* -------- Output: --------
dueDate                 new_dateTime
----------------------- -----------------------
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
2005-05-31 00:00:00.000 2005-05-31 01:00:00.000
*/

Are you seeing something different?  If not, please explain why are these results "wrong"?  Is it the '.000' that is bothering you?

Answer 13

These worked fine, I actually found 2 bad records in my datatset. Time was out of range 24:00:00.

Thanks