Convert datetime different results

I'm new to handling dates in T-SQL having mainly used the more forgiving Access.

I am trying to extract the date from a 40 chr field example below - dates are dd/mm/yy

Input By Angelab On 13/08/06 19:55:33

using

SELECT CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) AS EntryDate
FROM
(SELECT CommentDetails.Comments, InvoiceDetails.TransRef FROM (Transactions INNER JOIN CommentDetails ON Transactions.TransRef = CommentDetails.TransRef) INNER JOIN InvoiceDetails ON Transactions.TransRef = InvoiceDetails.TransRef WHERE ((Transactions.TransDate Between DATEADD(day,-5, '26-Dec-2004' ) And DATEADD(day,5, '01-Jan-2005' )) AND (SUBSTRING(CommentDetails.Comments,1,8)='Input by'))) AS A

WHERE (CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) Between '26-Dec-2004' And '01-Jan-2005' )

Although without the final WHERE it works fine, so the data is OK, with the final WHERE I get "out of range datetime value", how can it be valid in the SELECT statement but not the WHERE statement.

Our SQL server is set to american dates mm/dd/yyyy.

rgds

Peter

If you don't have just millions of rows, you could do something along these lines to find any values that won't work. You could just change the first block to a variable assignment if you don't want to have lots of output. 2005 is easier because of TRY...CATCH. For 2000 you could do something similar, but just with a dynamic SQL statement:

set nocount on
drop table test
go
create table test
(
comments varchar(40)
)
go
insert into test
select 'Input By Angelab On 13/08/06 19:55:33'
union all
select 'Input By Angelab On 13/09/06 19:55:33'
union all
select 'Input By Angelab On 13/13/06 19:55:33'
go
declare @.cursor cursor, @.comments varchar(40)
set @.cursor = cursor for (select comments from test)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.comments
if @.@.fetch_status <> 0
break
begin try
select CONVERT(datetime, SUBSTRING(@.Comments,22,8),3)
end try
begin catch
select @.comments as bad_value
end catch
end

Output:

--
2006-08-13 00:00:00.000

--
2006-09-13 00:00:00.000

--

bad_value
-
Input By Angelab On 13/13/06 19:55:33

|||

Never use BETWEEN with dates. Timestamps will kill you. It incorporates the time value as well. You will get inaccurate results.

Always use (date >= Today or date <=Tomorrow)

...and you're on the right track with the CONVERT function

Adamus

|||

History

The data has been downloaded into our archive SQL database, I'm extracting data from this 40 chrs text field that on the original machine was only informational and not "useable data".

For the CONVERT function I'm only extracting the date portion so all time values are 0.

CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) works perfectly well in the SELECT statement so the data is valid

where it doesn't work is

WHERE (CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) Between '26-Dec-2004' And '01-Jan-2005' )

Having tried many things I have a work around avoiding CONVERT by generating an unambigous date string.

My conclusion is that is that SUBSTRING(A.Comments,22,8) which returns 'dd/mm/yy' is implicitly and wrongly being converted into American date BEFORE it is being passed to CONVERT.. some of the time!!

The SAME function on the same data works in the SELECT but not in the WHERE.

I suspect the BETWEEN date AND date is forcing the implicit conversion early.

rgds

peter

|||

>>Having tried many things I have a work around avoiding CONVERT by generating an unambigous date string.<<

That was a good idea. I really wish you could have found a value that failed though. It would be very interesting to figure out what the actual problem was (though probably not as much to you as it would be to me at this point :) One suggestion I would have made would be to put the substring into the derived table so you only had to do this convert once, but that wouldn't have worked if the data was still bad.

Good luck