Update time from a datetime column, using another datetime column.

Question

Update time from a datetime column, using another datetime column.

Mike Menser

Hall of Fame

Points: 3679
More actions
August 9, 2010 at 11:27 am

#227618

I have been searching and finding nothing. Is there a simple syntax that I can use to achieve what I need? I have an old application that has 2 datetime columns. One that was for the date, one that was for the time. So in one of those datetime columns the times are accurate but the dates are all the default dates. On the other, the dates are correct but the times are all 0's. This is the premise of what I am trying to do... any ideas would be appreciated:
update tablenamehere
set new_datetimecolumn = REPLACE(new_datetimecolumn,DATEPART(HOUR,new_datetimecolumn),DATEPART(HOUR,old_timecolumn))

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike Menser Hall of Fame Points: 3679 More actions · Answer 1

I apologize, I forgot to mention that the above code produces an error: Conversion failed when converting datetime from character string.

Not too sure what I would need to convert.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 2

It seems there should be a more efficient way to do this, but a crude form would be like:

declare @date datetime

declare @time datetime

set @date = getdate()

set @time = cast('01/01/2000 03:12:12.321' as datetime)

select @date, @time

select (convert(varchar(10),@date, 101) + ' ' + cast(datepart(hh,@time) as varchar) + ':' + cast(datepart(mi, @time) as varchar) )

Mike Menser Hall of Fame Points: 3679 More actions · Answer 3

but see I already have the dates and the times, it is just that the times are in one datetime column and the dates are in another. I tried something crude myself to see if I could view the data successfully. I created a varchar column, and then updated it with the following information:

update new_tablename

set new_devcol = left(new_requesteddateandtime,11) + ' ' + right(new_escortreqtime,6)

the output of the new column is correct:

Jan 6 2005 9:45PM

I could be able to convert this varchar column to a datetime column now that I have the correct data in ONE column, but I get an arithmatic overflow error when trying to convert it.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 4

Mike Menser (8/9/2010)
...the output of the new column is correct:
Jan 6 2005 9:45PM
I could be able to convert this varchar column to a datetime column now that I have the correct data in ONE column, but I get an arithmatic overflow error when trying to convert it.

How are you doing the "convert to datetime"?

Also, it would be useful if you could prepare a sample table schema with maybe 2 or 3 rows so that everybody attempting this have consistent data.

My example was just to show the conversion based on two variables but I would guess replacing the variables with column names in the formula should have given a consistent result.

update new_tablename

set new_devcol = left(new_requesteddateandtime,11) + ' ' + right(new_escortreqtime,6)

I would not recommend using "left" and "right" method calls while fiddling with date data. One reasons for that would be the date formats used. 11 characters in Jan 20 2005 9:45 PM and 2005-01-20 09:45:00.000 will not yield consistent result.

Mike Menser Hall of Fame Points: 3679 More actions · Answer 5

I used the standard format along with the left and right operators to concatenate the data into 1 varchar column. It came in the wrong way the first time, which is why I got the arithmatic error. I corrected and it came over properly and I was able to convert. I ran an update on the column I was referencing with the date AND time in the same column and everything looks great now. Thanks for looking though!

First:

update new_psgescortsextensionbase

set new_devcol = left(new_requesteddateandtime,11) + ' ' + right(new_escortreqtime,7)

Then I tested the output by selecting it out as a datime:

select convert(DATETIME,new_devcol,100) from new_psgescortsextensionbase

And finally, the update:

update new_psgescortsextensionbase

set new_requesteddateandtime = convert(DATETIME,new_devcol,100)

(i know the formatting of 100 is the default, but you can never be TOO safe!)

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 6

Mike Menser (8/9/2010)
I have been searching and finding nothing. Is there a simple syntax that I can use to achieve what I need? I have an old application that has 2 datetime columns. One that was for the date, one that was for the time. So in one of those datetime columns the times are accurate but the dates are all the default dates. On the other, the dates are correct but the times are all 0's. This is the premise of what I am trying to do... any ideas would be appreciated:

Assumptions:

The date field has 00:00:00 for the time, and the time field contains spurious dates (maybe the date the value was entered? or are they all '1-1-1900', which is a default date?). Is that correct?

You want to combine the date portion from the first date with the time portion of the second date.

Try the following

declare @d1 datetime

declare @t1 datetime

set @d1 = '05/15/2010 00:00:00'

set @t1 = '3:15:15.000'

declare @dCombo datetime

set @dCombo = @d1 + DateAdd(dd, DateDiff(dd, @t1, '1-1-1900'), @t1)

print @dCombo

The DateAdd/DateDiff extracts the time from the second date field (assuming your system uses a default date of 1-1-1900 -- not sure all systems will?) . Simple addition will join the two.

Rob Schripsema
Propack, Inc.

drew.allen SSC Guru Points: 76985 More actions · Answer 7

Rob Schripsema (8/9/2010)
Assumptions:
The date field has 00:00:00 for the time, and the time field contains spurious dates (maybe the date the value was entered? or are they all '1-1-1900', which is a default date?). Is that correct?
You want to combine the date portion from the first date with the time portion of the second date.
Try the following
declare @d1 datetime
declare @t1 datetime
set @d1 = '05/15/2010 00:00:00'
set @t1 = '3:15:15.000'
declare @dCombo datetime
set @dCombo = @d1 + DateAdd(dd, DateDiff(dd, @t1, '1-1-1900'), @t1)
print @dCombo
The DateAdd/DateDiff extracts the time from the second date field (assuming your system uses a default date of 1-1-1900 -- not sure all systems will?) . Simple addition will join the two.

You can actually simplify this a little and it has the added advantage that it works without resorting to your assumptions. That is, as long as the one field has the correct date and the other has the correct time, it doesn't matter what time the first field has and it doesn't matter what date the second field has. You also don't have to resort to using plain arithmetic.

declare @d1 datetime

declare @t1 datetime

set @d1 = '05/15/2010 12:34:56'

set @t1 = '1/2/1934 03:15:15.000'

declare @dCombo datetime

set @dCombo = DateAdd(dd, DateDiff(dd, @t1, @d1), @t1)

SELECT @dCombo

All I did was replace the hard-coded '1/1/1900' with the date variable and removed the plain addition.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 8

drew.allen (8/9/2010)You can actually simplify this a little and it has the added advantage that it works without resorting to your assumptions. That is, as long as the one field has the correct date and the other has the correct time, it doesn't matter what time the first field has and it doesn't matter what date the second field has. You also don't have to resort to using plain arithmetic.
declare @d1 datetime
declare @t1 datetime
set @d1 = '05/15/2010 12:34:56'
set @t1 = '1/2/1934 03:15:15.000'
declare @dCombo datetime
set @dCombo = DateAdd(dd, DateDiff(dd, @t1, @d1), @t1)
SELECT @dCombo
All I did was replace the hard-coded '1/1/1900' with the date variable and removed the plain addition.
Drew

Great catch. Much improved.

Rob Schripsema
Propack, Inc.