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

Question

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)

November only has 30 days, 20131131 can't be converted to a date because it doesn't exist — ovaltein, Nov 08 '13 at 20:38
Damn ... that's right, tought it didn´t work for any 31 day but it did worked with '20131231' ... ashamed :S — code4jhon, Nov 08 '13 at 20:42

score 1 · Accepted Answer · answered Nov 14 '13 at 15:01

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.

1 Answers1