Updating just the time portion datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating just the time portion datetime

Author

Topic

richcol
Starting Member

5 Posts

Posted - 2007-11-09 : 06:05:44

Hi All,

I have a table that is storing a datetime field. The problem is that the value has until recently been entered as a date and time. Now it only stores the date and the time defaults to '00:00:00.000'.

What I am trying to do is update just the time portion of the date but without success so that all of the dates have the same time set against them.

I have tried:

UPDATE datetest
SET date1 = '00:00:00.000"

but this resets the date back to 1900

I tried this:

update datetest
set date1 = date1 + datepart(hh, 00, date1)

but this didnt do anything.

Not really sure where to start, any advice appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 06:31:20

UPDATE Table1
SET Col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', Col1), '19000101')

E 12°55'05.25"
N 56°04'39.16"

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-09 : 11:28:53

quote:
Originally posted by Peso

UPDATE Table1
SET Col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', Col1), '19000101')

E 12°55'05.25"
N 56°04'39.16"

I do not think that works

declare @t table(col1 datetime)

insert @t
select '11/02/2007'
union all select '10/16/2007'
union all select '09/01/2005'
union all select '02/10/1996'

-- check
select * from @t

-- main work
UPDATE @t
SET Col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', Col1), '19000101')

-- check, nothing changes!
select * from @t

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-09 : 11:32:33

quote:
Originally posted by johnsql

quote:
Originally posted by Peso

UPDATE Table1
SET Col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', Col1), '19000101')

E 12°55'05.25"
N 56°04'39.16"

I do not think that works

declare @t table(col1 datetime)

insert @t
select '11/02/2007'
union all select '10/16/2007'
union all select '09/01/2005'
union all select '02/10/1996'

-- check
select * from @t

-- main work
UPDATE @t
SET Col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', Col1), '19000101')

-- check, nothing changes!
select * from @t

Since your test data doesn't contain times, what would you expect to change?

See what happens with a datetime like this '20071016 12:34:02.993'

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-09 : 11:36:56

Actually it works. The OP wanted to remove the time portion of the datatime or rather set the time to 00:00:00.

Your sample data does not contain time that's why there isn't any different before and after the update statement

DECLARE @t TABLE(col1 datetime)

INSERT @t
SELECT '11/02/2007 12:45'
UNION ALL SELECT '10/16/2007 23:45'
UNION ALL SELECT '09/01/2005'
UNION ALL SELECT '02/10/1996'

-- CHECK
SELECT * FROM @t
/*
col1                                                   
------------------------------------------------------ 
2007-11-02 12:45:00.000
2007-10-16 23:45:00.000
2005-09-01 00:00:00.000
1996-02-10 00:00:00.000

(4 row(s) affected)
*/

-- main work
UPDATE @t
SET col1 = DATEADD(DAY, DATEDIFF(DAY, '19000101', col1), '19000101')

-- CHECK, nothing changes!
SELECT * FROM @t
/*
col1                                                   
------------------------------------------------------ 
2007-11-02 00:00:00.000
2007-10-16 00:00:00.000
2005-09-01 00:00:00.000
1996-02-10 00:00:00.000

(4 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 13:02:04

the time portion of the date is always there.
format it in the front end.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-09 : 14:11:40

Thank you. I see your points.

richcol
Starting Member

5 Posts

Posted - 2007-11-12 : 05:38:14

Many thanks,

that was exactly what I needed.

thanks again

Colin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources