I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1
Syntax error converting date time from character string". can someone please help.
SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime),
CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime))
AS StopMinutes,
BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA,
BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME,
OQTCOD, BAADES, PCS, WGT, Tractor, Driver
FROM dbo.JCI_Delivery_Report
Make sure you are constructing the datetime string correctly. You have to make sure the string you are constructing is a string SQL Server understands as a datetime; otherwise you're going to get a casting error.
German Afanador
|||Can you explain what you are trying to do?|||SUBSTRING(SCHTIME, 3,4) should be SUBSTRING(SCHTIME, 3,2)
SUBSTRING(ACTTIME, 3,4) should be SUBSTRING(ACTTIME, 3,2)
and while I think it's bad that you've created text fields in your database for dates and times instead of storing them as they should be (in datetime format), this will get you closer to what you want.
