Search code examples
sqlselectsybasesqldatetime

sybase get records between two dates, being the dates expressed as VARCHARS

When I execute:    

SELECT some_fields 
FROM some_table  
WHERE some_other_field BETWEEN '20130901' AND '20131131'

I get Error (247) Arithmetic overflow during implicit conversion of VARCHAR value '20131131' to SMALLDATETIME field.** 

But when I execute:

SELECT some_field  
FROM some_table 
WHERE some_other_field BETWEEN  '20130901' AND '20131130'

no complains.  (Sybase 15.7.0)

Solution

  • November only has 30 days, 20131131 can't be converted to a date because it doesn't exist. Change the where clause in your first statement from

    WHERE some_other_field BETWEEN '20130901' AND '20131131'

    to this

    WHERE some_other_field BETWEEN '20130901' AND '20131130'

    It should execute no problem.