Using between satement for dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using between satement for dates?

Author

Topic

louiedogg418
Starting Member

28 Posts

Posted - 2011-04-18 : 12:43:19

trying to do this

select first_name,Last_name,hire_Date
from employee_1
where hire_date between 1/1/1999 and 1/1/2003

and getting this error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '10/7/2003 ' to data type int.

my date values in the table create statement are CHAR and not INT...

What is the deal?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 12:50:16

If you add single quotes around the dates, that will stop the compiler from complaining.. BUT

select first_name,Last_name,hire_Date
from employee_1
where hire_date between '1/1/1999' and '1/1/2003'

The hire_date column should have been a date type column such as datetime or smalldatetime. If you are unable to change it, do the query like this:

select first_name,Last_name,hire_Date
from employee_1
where cast(hire_date as datetime) between '1/1/1999' and '1/1/2003'

That is, cast the hire_date as datetime AND put single quotes around the 1/1/1999 and 1/1/2003.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-18 : 12:50:41

Heres a clue: 1 divided by 1 divided by 1999 is an int value of 0.

louiedogg418
Starting Member

28 Posts

Posted - 2011-04-18 : 13:19:21

Ahhhh No duh! if u got to have single qoutes around the values when creating and inserting CHAR values it would make sense to have them when reporting! thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources