Updating Time value to a date/time value

[TimBiesiek]

Hi all,

I have a column in a table that is storing just a date, and another column that used to store just the time. This 2nd column is now storing both the date and time (1/07/2005 11:57:00).

However, I have some old data that only has the time in this column. I want to update these records to have the date and time, but can't find an easy way to do it.

I have created an update query as below, but this updates the time field to 2 days before the date field...

UPDATE    arc_FieldResult
SET              RTime = RDate + ' ' + RTime
WHERE     (SUBSTRING(CONVERT(varchar(50), RTime), 1, 11) = 'Dec 30 1899')

For example, if the date was 10/07/2005 and the time was 11:57:00, this query would update the time field to show 8/07/2005 11:57:00.

Note that in the query, the where clause only pulls up the records that only have the time in...

Any help would be great thanks!

 

[cognoscognos]

Try using || instead of +
something like this:
SET RTime = RDate || ' ' || RTime

 

[TimBiesiek]

Ok, will try that and see how it goes... Thanks!

 

[amrita418]

The syntax that cognos suggested does not work in sql server. Its an oracle concatenation operator.

Since both values are datetime + should sum them up.
Sample Code

create table #TableDate (v_date datetime)
insert into #TableDate values ('10/7/2003')

select v_date + '11:57:00' from #TableDate

Regards,
AA

 

[TimBiesiek]

Hi AA,

Thanks, but I actually want to update the records, not insert new....

I have figured out a way around it as below:

UPDATE    arc_FieldResult
SET              RTime = CONVERT(char(10), RDate, 101) + ' ' + CONVERT(char(10), RTime, 108)
WHERE     (SUBSTRING(CONVERT(varchar(50), RTime), 1, 11) = 'Dec 30 1899')