Exporting Date and Time data from Law for Import into Relativity

gistfile1.sql

-- Before running, you may need to perform a find/replace on "documentid" to the name of your document number field.

WITH dates AS (
	SELECT 
		documentid,
		id,
		attachpid,
		CONVERT(nvarchar,  [DateRcvd], 102) + ' ' + [TimeRcvd] as DateTimeRcvd,
		CONVERT(nvarchar,  [DateSent], 102) + ' ' + [TimeSent] as DateTimeSent,
		CONVERT(nvarchar,  [DateLastPrnt], 102) + ' ' + [TimeLastPrnt] as DateTimeLastPrnt,
		CONVERT(nvarchar,  [DateLastMod], 102) + ' ' + [TimeLastMod] as DateTimeLastMod,
		CONVERT(nvarchar,  [DateCreated], 102) + ' ' + [TimeCreated] as DateTimeCreated,
		CONVERT(nvarchar,  [DateAppEnd], 102) + ' ' + [TimeAppEnd] as DateTimeAppEnd,
		CONVERT(nvarchar,  [DateAppStart], 102) + ' ' + [TimeAppStart] as DateTimeAppStart,
		CONVERT(nvarchar,  [DateAccessed], 102) + ' ' + [TimeAccessed] as DateTimeAccessed,
		COALESCE(
			CONVERT(nvarchar,  [DateSent], 102) + ' ' + [TimeSent], 
			CONVERT(nvarchar,  [DateRcvd], 102) + ' ' + [TimeRcvd], 
			CONVERT(nvarchar,  [DateLastMod], 102) + ' ' + [TimeLastMod],
			CONVERT(nvarchar,  [DateCreated], 102) + ' ' + [TimeCreated], 
			CONVERT(nvarchar,  [DateAccessed], 102) + ' ' + [TimeAccessed], 
			CONVERT(nvarchar,  [DateAppStart], 102) + ' ' + [TimeAppStart], 
			CONVERT(nvarchar,  [DateAppEnd], 102) + ' ' + [TimeAppEnd]
		) as PrimaryDate
	
	FROM tbldoc 
	-- If there is more than one export, you may need to care about this. 
	-- But if you ignore it, the worst that happens is you re-overlay documents with the same data again.
	-- where EDSessionid in ( 1,2 ) 
)
SELECT 
	d.documentid,
	COALESCE(d.datetimercvd,'') as datetimercvd,
	COALESCE(d.datetimesent,'') as datetimesent,
	COALESCE(d.datetimelastprnt,'') as datetimelastprint,
	COALESCE(d.datetimelastmod,'') as datetimelastmod,
	COALESCE(d.datetimecreated,'') as datetimecreated,
	COALESCE(d.datetimeappend,'') as datetimeappend,
	COALESCE(d.datetimeappstart,'') as datetimeappstart,
	COALESCE(d.datetimeaccessed,'') as datetimeaccessed,
	COALESCE(d.primarydate, '') as primarydatetime,
	COALESCE(p.primarydate, '') as parentdatetime,
	p.documentid as groupid 
FROM
	dates d, dates p
WHERE
	(d.attachpid > 0 and p.ID=d.attachpid) OR  (d.attachpid = 0 and p.ID=d.id)