sqlmaverick (10/5/2015)
Actually , for historical record I have to update End date as Updated Date where end date is Default date '2099-12-31'and for new record with same id , have to insert with Default date '2099-12-31'.But it is not updating default the end date when there is date value. But when I loaded with Null as Enddate it is updating. It is working for Null and not working for Dates.
I am trying to figure out from one day , it is not helping me out. So please any one got a chance try to give some tips. Please refer below query for better understanding.
No Luck--- UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] ='2015-07-19 00:00:00.000'
Working for this querry UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] IS NULL
GetDate() returns a DateTime, not just a date.
If EndDate should contain only a date, update its Data Type to Date, to avoid storing the time component.
If EndDate should contain a datetime, your WHERE clause needs to change:
... WHERE [MemberNumber] = 1041 AND cast([EndDate] as date) ='20150719'
--Edit: Changed date format to ISO standard YYYYMMDD
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.