T-SQL Code: A Procedure Using CASE Structures to Format Times

CREATE Procedure ListGo2Events

SET NOCOUNT ON

-- Collect data into temp' table. SELECT tblVenueEvents.EventID , tblVenueEvents.ArtistSummary , tblVenueEvents.ArtistExtended , tblVenueEvents.VenueID , tblVenueEvents.DoorDate , tblVenueEvents.DoorTime , tblVenueEvents.EventDate , tblVenueEvents.EventTime , tblVenueEvents.GenreID , sch_t_genre.genre_name , tblVenueEvents.AgeRestriction , tblVenueEvents.SoldOutIndicator , tblVenueEvents.ChangedToDate , tblVenueEvents.ChangedToTime , tblVenueEvents.Canceled INTO #out FROM tblVenueEvents , sch_t_genre WHERE tblVenueEvents.GenreID = sch_t_genre.genre_id AND MONTH(tblVenueEvents.EventDate) >= MONTH(GETDATE()) AND DAY(tblVenueEvents.EventDate) >= DAY(GETDATE()) AND YEAR(tblVenueEvents.EventDate) >= YEAR(GETDATE())

-- Attempt to format Event times...

-- First sweep... UPDATE #out SET DoorTime = ( CASE WHEN DoorTime LIKE '%[^1-9]1:%' THEN '13' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]2:%' THEN '14' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]3:%' THEN '15' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]4:%' THEN '16' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]5:%' THEN '17' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]6:%' THEN '18' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]7:%' THEN '19' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]8:%' THEN '20' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]9:%' THEN '21' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]10:%' THEN '22' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) WHEN DoorTime LIKE '%[^1-9]11:__%' THEN '23' + SUBSTRING(DoorTime,CHARINDEX(":",DoorTime) + 1,2) END ) WHERE CHARINDEX(':',DoorTime) > 0

UPDATE #out SET EventTime = ( CASE WHEN EventTime LIKE '%[^1-9]1:%' THEN '13' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]2:%' THEN '14' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]3:%' THEN '15' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]4:%' THEN '16' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]5:%' THEN '17' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]6:%' THEN '18' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]7:%' THEN '19' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]8:%' THEN '20' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]9:%' THEN '21' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]10:%' THEN '22' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) WHEN EventTime LIKE '%[^1-9]11:__%' THEN '23' + SUBSTRING(EventTime,CHARINDEX(":",EventTime) + 1,2) END ) WHERE CHARINDEX(':',EventTime) > 0

UPDATE #out SET ChangedToTime = ( CASE WHEN ChangedToTime LIKE '%[^1-9]1:%' THEN '13' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]2:%' THEN '14' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]3:%' THEN '15' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]4:%' THEN '16' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]5:%' THEN '17' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]6:%' THEN '18' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]7:%' THEN '19' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]8:%' THEN '20' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]9:%' THEN '21' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]10:%' THEN '22' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) WHEN ChangedToTime LIKE '%[^1-9]11:__%' THEN '23' + SUBSTRING(ChangedToTime,CHARINDEX(":",ChangedToTime) + 1,2) END ) WHERE CHARINDEX(':',ChangedToTime) > 0

-- Second sweep... UPDATE #out SET DoorTime = RTRIM(DoorTime) + '00' WHERE LEN(DoorTime) = 2

UPDATE #out SET EventTime = RTRIM(EventTime) + '00' WHERE LEN(EventTime) = 2

UPDATE #out SET ChangedToTime = RTRIM(ChangedToTime) + '00' WHERE LEN(ChangedToTime) = 2

-- Project result. SELECT EventID , ArtistSummary , ArtistExtended , VenueID , DoorDate , DoorTime , EventDate , EventTime , GenreID , genre_name , AgeRestriction , SoldOutIndicator , ChangedToDate , ChangedToTime , Canceled FROM #out

mod date: 2000-12-24T22:50:05.000Z