I've observed what seems to be odd behavior (or it's something I'm doing odd). I'm trying to something that is very simple - insert the date/time into a column defined as a datetime using getdate(), but I'm not always getting the time portion. For example, I noticed on the customers (SQL 7) system the result of a getdate stored in a column is: dd/mm/yyyy 12:00:00 regardless of the current time. On my system (SQL 2000) I was getting this same behavior but at some point it changed to dd/mm/yyyy hh:mm:ss.xxx (not 12:00:00) as I would expect. The only thing I can think I did was to delete all the rows out of the table and start over, but I'm not certain. A colleague of mine (SQL 2000) is getting the same incorrect result as the customer. Specifically, the code looks like this, where rss_level_date is the offending column. (I'm using a variable to hold the time since it is used later in the procedure and I want the time to be same for another field for comparison purposes. I don't think this should matter.) declare @now as datetime set @now = getdate() UPDATE DM_Rate_Source SET rss_level= 'AR', rss_level_date=@now WHERE( (SELECT rss_mrs_id FROM DM_Rate_Source WHERE rss_id=@rss_id)=rss_mrs_id AND rss_level = 'PU' AND (rss_id not in (select rss_id from dm_rate_source where (SELECT rss_effective_date FROM DM_Rate_Source WHERE rss_id=@rss_id)=rss_effective_date AND (SELECT rss_level_date FROM DM_Rate_Source WHERE rss_id=@rss_id)=rss_level_date)))
DM_Rate_Source is defined as: create table "DM_Rate_Source" ( "rss_id" int identity not null, "rss_mrs_id" int not null, "rss_blob_id" int null, "rss_gmir" numeric(4,2) not null, "rss_effective_date" datetime not null, "rss_level" varchar(2) not null, "rss_level_date" datetime not null, "rss_date_added" datetime not null)
Any help is appreciated!!-- Jeff
Getdate() not returning time
Posted - 2004-03-03 : 22:52:04
