Add date to time

I have a column that i need to update.

column - DepartureDateTime - datetime
column - DepartureDateTime - varchar(max)

Example - 2012-03-12 00:00:00.000 + 15:36
2012-03-12 15:36:00.000

If i do a select its ok, but not an update.

Example
SELECT ,CAST([DepartureDateTime] AS datetime) + CAST (DepartureTime AS datetime) AS dt

Open in new window


Update
  UPDATE table
  SET [DepartureDateTime] = CAST([DepartureDateTime] AS DATETIME) + CAST([DepartureTime] AS DATETIME)

Open in new window


I get The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
aneilgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What this returns?
SELECT CAST([DepartureDateTime] AS datetime) + CAST (DepartureTime AS datetime) AS dt
0
aneilgAuthor Commented:
My mistake.

It returns
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, that is expected.
What are you trying to do? You can only add integers (days) to a date.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
It depends on the boundary conditions. E.g. when it's really a DATE, thus your TIME part is always 00:00:00, then this works:

DECLARE @DepartureDateTime1 DATETIME;
DECLARE @DepartureDateTime2 VARCHAR(MAX);

SET @DepartureDateTime1 = '20120312 00:00:00.000';
SET @DepartureDateTime2 = '15:36';

SELECT  DATEADD(DAY, DATEDIFF(DAY, CAST(0 AS DATETIME), @DepartureDateTime1), CAST(@DepartureDateTime2 AS DATETIME));

Open in new window

0
aneilgAuthor Commented:
Hello,

No my mistake.

i need to do the following.

I have a column that i need to updating.

column - DepartureDateTime - datetime
column - DepartureDateTime - varchar(max)

Example - 2012-03-12 00:00:00.000 + 15:36
2012-03-12 15:36:00.000
0
aneilgAuthor Commented:
I basically need to add the time part to the datetime field.

So i can compare dates.


thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I still don't understand it.
You have 2 columns with same name and different data types?
What those examples are for?
0
aneilgAuthor Commented:
Just another point I need to update 1000's of columns not just the one.
0
aneilgAuthor Commented:
I Have two different columns.

column 1 - DepartureDateTime data type datetime
column 2 - DepartureTime data type navchar(max)

I need to update column1 by combining both columns.

But because one column is varchar i get an error.

Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please give us sample data (just some date columns and without sensitive data) so we can work on it.
0
aneilgAuthor Commented:
DepartureDateTime
2012-03-12 00:00:00.000
2013-08-27 00:00:00.000
2005-02-05 00:00:00.000
2012-02-03 00:00:00.000
2010-01-29 00:00:00.000
2010-03-30 00:00:00.000
2008-03-10 00:00:00.000
2012-06-14 00:00:00.000
2012-11-24 00:00:00.000
2007-03-31 00:00:00.000

Open in new window


DepartureTime
15:36
19:32
05:40
09:45
13:27
01:43
13:03
22:12
03:21
16:44

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, I hope that I understood what are you looking for.
Please check this:
SELECT CAST((CAST(CAST(DepartureDateTime AS DATE) AS VARCHAR) + ' ' + DepartureTime) AS DATETIME)

Open in new window

0
aneilgAuthor Commented:
Thanks but i still get

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

column 2 - DepartureTime data type navchar(max)
0
aneilgAuthor Commented:
I think i have a dodgy time varchar(max)

IF i do a select top X it works.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you must have a bad time format somewhere. My tests went successfully.
0
aneilgAuthor Commented:
These results are causing the problem

DepartureDateTime 24:00

Any suggestions.

Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
24:00 doesn't exist. Or you change it to '00:00' or '23:59' depending on how do you want to handle it.
0
aneilgAuthor Commented:
Thanks for your help Vitor.

I just changed it.

Thanks again.
0
aneilgAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for aneilg's comment #a40796441

for the following reason:

Good advice
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please check which comment you want to chose as solution since you're accepting your own comment.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aneilgAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.