SQL for changing time part of a DateTime field 2

[LucieLastic]

hi All

I currently have a table containing a DateTime field and the field has values like:-

01/05/2004 00:00:35
01/05/2004 00:00:30
01/05/2004 00:00:10
01/05/2004 00:00:40
02/05/2004 00:01:02
02/05/2004 00:00:55
02/05/2004 00:00:33
02/05/2004 00:00:35
02/05/2004 00:00:40

I'd like to update the column and keep the date part but change the time part to 00:00:01. I have some SQL, would this do the trick and is it the best way to do it?

Update CALCSTABLE
set CalcDateTime = trunc(CalcDateTime) + 0.0000157407

many thanks in advance for any help
lou

 

[lewisp]

Or

UPDATE calcstable
SET caldatetime = To_Date(To_Char(calcdatetime,'DD/MM/YYYY') || '00:00:01','DD/MM/YYYYHH24:MI:SS');

 

[LucieLastic]

Wow, thanks lewisp. Will your version run faster or do you think they'll be about the same?

I need to get my book out and find out what exactly 'DD/MM/YYYYHH24:MI:SS' and || does.

Thanks again for the reply,
lou

 

[sem]

Lou, your own solution is much faster, but do you need to update millions of records? If not the differens would be insignificant.

Regards, Dima

http://seminihin.narod.ru

 

[LucieLastic]

hi Dima

I think the number of records is more 100s of thousands rather than millions. I know that one of the tables I need to update has approx 500,000 records but there are other tables also involved and I'm awaiting notification from one of the db users on which ones.

Thanks for the info,
lou

 

[sem]

Ok, 500,000 is enough to see the difference

Regards, Dima

http://seminihin.narod.ru