SQL Server Performance

Query Problem.....Out Of Range ...value

Discussion in 'SQL Server 2005 General DBA Questions' started by L0st_Pr0phet, Nov 12, 2008.

  1. L0st_Pr0phet Member

    Ok here is the problem, when I run the below script I get...Msg 296, Level 16, State 3, Line 44
    The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
    Msg 295, Level 16, State 3, Line 44
    Syntax error converting character string to smalldatetime data type.
    But when I run it with the where clause line commented it out it works fine, this sounds obvious but I am using the same conversion for one of the select columns and returns the column records fine.....all of which are valid dates! Any ideas?
    Script:declare
    @CustomerID as nvarchar(25)set @CustomerID ='3042759' -- for arivals '3042759' select d
    .company_name,a
    .field_1,a
    .field_25,Substring
    (a.field_1,11,8)as Expected_Arrival_Date,convert
    (smalldatetime,(Substring(a.field_1,11,8))) as test,Substring
    (a.field_1,20,5)as Expected_Arrival_Time,Substring
    (a.field_1,25,8)as Expected_Leave_Date,Substring
    (a.field_1,34,5)as Expected_Leave_Timefrom
    sys_custom_field_data a left
    outer join JM_Work_Order b ON a.custom_field_data_no = b.custom_field_data_noinner
    join JM_Job c ON b.job_no = c.job_noinner
    join sys_phone_list d on c.cust_id = d.list_idwhere c.cust_id = @CustomerID and b
    .custom_field_data_no is not null and a.field_1 is not null
    and
    convert(smalldatetime,(Substring(a.field_1,11,8))) between dateadd(dd,-2,getdate()) and dateadd(dd,7,getdate())Order
    by Substring(a.field_1,11,8) desca.field_1 is char on source table. Any ideas would be great!
  2. Madhivanan Moderator

    Try adding this condition tooand
    isdate(convert(smalldatetime,(Substring(a.field_1,11,8))))=1
    and len(Substring(a.field_1,11,8))=8
  3. L0st_Pr0phet Member

    Excellent that got it
    thanks
    stew
  4. omaleem New Member

    Below would be a good article to read on this topic... just my 2 cents:
    http://msdn.microsoft.com/en-us/library/ms187347.aspx
    Thanks,
    Omair
  5. Madhivanan Moderator

Share This Page