Update DateTime Field

[warrenk]

I am trying to update a date time field (it is currently NULL) and can't get it to update.

I am using the following SQL...

update table1 set livedate = '2006-12-01 00:00:00.000' where livedate is null

When I run the SQL, it runs successfully...but nothing gets updated.

Thanks for any help!
Warren

 

[elsenorjose]

Then it's not running successfully

What data type is that field? If it's not a datetime it won't update correctly. Also, most datetime fields don't have milliseconds, IIRC. Try dropping the 3 0s after your seconds.

Also, are you sure it's NULL and not a space or series of spaces? If it's not NULL and is actually a space, your query wouldn't evaluate to TRUE and it wouldn't update.

 

[mrdenny]

If it's an actual datetime or smalldatetime field then it can't be a space or series of spaces.

If you are setting it to midnight, you don't need to include the time. A date with no time is midnight of that day. You can also try casting the value as datetime or smalldatetime to make sure that it's in the same datatype as your field.

update  table1 
set livedate = cast('2006-12-01' as datetime) 
where livedate is null

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[r937]

warren, what database management system are you on?

you've posted in the ANSI SQL forum

we need to know because date considerations vary so much from one dbms to the next

r937.com | rudy.ca

 

[JarlH]

ANSI SQL solution:
[tt]update table1 set livedate = TIMESTAMP'2006-12-01 00:00:00.000'
where livedate is null [/tt]