Quick & Dirty CRM Date Change
Goodbye To Yesterday...
Every so often I find I need to update various date fields in my CRM demo data for a last-minute customer demo. A typical example is when I'm demonstrating case management functionality and I want to update the CreatedOn or ModifiedOn date for all my cases, offset by some random number of days from a particular date. The easiest way to do this is by running a SQL script directly on the CRM database, but enyone who knows me well knows that I have this mental block when it comes to writing T-SQL code. So instead of spending hours trying to figure it out each time I want to make this kind of change, I thought I would post some typical T-SQL code here for me to re-use.
DECLARE @CreatedOn datetime, @IncidentId uniqueidentifier
DECLARE incident_cursor CURSOR FOR
SELECT incidentid
FROM incident
OPEN incident_cursor
FETCH NEXT FROM incident_cursor
INTO @IncidentId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CreatedOn = DATEADD(Day, ROUND(RAND()*365,0)-365, GETDATE())
UPDATE incident
SET createdon = @CreatedOn
WHERE incidentid = @IncidentId
FETCH NEXT FROM incident_cursor
INTO @IncidentId
END
CLOSE incident_cursor
DEALLOCATE incident_cursor
Please feel free to use this as you see fit, but remember folks that any kind of direct CRM database modification is totally unsupported and could lead to all kinds of mayhem, premature hair loss and other nasties, so don't try this on your production systems.
This posting is provided "AS IS" with no warranties, and confers no rights.
Comments
Anonymous
April 24, 2008
PingBack from http://microsoftnews.askpcdoc.com/microsoft-dynamics-crm-40/quick-dirty-crm-date-changeAnonymous
May 18, 2008
Inspired by Simon Hutson's post and sharing his aversion to T-SQL code, I decided to have a go atAnonymous
June 22, 2008
The comment has been removed