Date Diff

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.

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--