DECLARE @StartDate_Time DATETIME, @EndDate_Time DATETIME
SELECT @StartDate_Time = CONVERT(DATETIME,'06/06/2012',103), @EndDate_Time = CONVERT(DATETIME,'07/06/2012',103)
SELECT @StartDate_Time, @EndDate_Time
SELECT
x.ProperStartDateTime,
d.*
FROM SLADB.dbo.ProdDataTB d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
FROM (VALUES ('01','Jan'),('02','Feb'),('03','Mar'),('04','Apr'),('05','May'),('06','Jun'),
('07','Jul'),('08','Aug'),('09','Sep'),('10','Oct'),('11','Nov'),('12','Dec')) x (MonthNum, MonthName)
WHERE MonthName = SUBSTRING(d.StartDate_Time,5,3)
) p
CROSS APPLY (SELECT ProperStartDateTime = CONVERT(DATETIME,p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],120)) x
WHERE d.MachineName = 'APS01'
AND x.ProperStartDateTime >= @StartDate_Time AND x.ProperStartDateTime < @EndDate_Time
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden