SQL script to take Schoolbase calendar, sanitise it and make it a viable import for csv into Google Calendar. The script with take all events going back 35 days, and all future events. It will NOT include any events where the word "prov" exists int the details (as in provisional booking etc).

SchoolbaseToGoogleCalendar

SELECT TOP (100) PERCENT
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EvName, ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ',
	' ') AS subject,
	LEFT(CONVERT(varchar, CONVERT(date, CalDate)), 10) AS [start date],
	CONVERT(time, ISNULL(EvTime, CalDate)) AS [start Time],
	LEFT(CONVERT(varchar, CONVERT(date, CalDateEnd)), 10) AS [end date],
	CONVERT(time, ISNULL(EvTimeEnd, CalDateEnd)) AS [end Time],
	(CASE
		WHEN CONVERT(time, EvTime) IS NULL THEN 'True' ELSE 'False'
	END) AS [all day event],
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EvDetails + ' (last updated: ' + CONVERT(varchar(10), GETDATE(), 103)
	+ ' at ' + CONVERT(varchar(5), GETDATE(), 108) + ')', ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ', ' ') AS description,
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EvVenue, ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ', ' ') AS location,
	'false' AS private
FROM dbo.Events
WHERE (CalDate > GETDATE() - 35) AND (LOWER(EvDetails) NOT LIKE '%prov%')