query taht run in sql 2000 run no more in sql 2014

Help please
a query that run ok under sql 2000 run no more in sql 2014

insert into chequetmp select * from nopagados where convert (datetime,estimado)>= '02/09/2015' and convert(datetime,estimado) <= '02/09/2015'
help!!
ErnestoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
By run no more, do you mean you are getting an error, or that you are not receiving expected results?
0
ErnestoAuthor Commented:
getting an error but in 2014 engine, not in 2000 where my application was before
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

in 2000 works perfectly, please help!!
0
Lee SavidgeCommented:
I suspect the date setting is different, ie mm/dd/yyyy verses dd/mm/yyyy.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaun KlineLead Software EngineerCommented:
Try adding the number format to your convert functions:
convert(datetime,estimado, 103) <<103 is the dd/mm/yyyy format.

You can see the full list of date formats here: https://msdn.microsoft.com/en-us/library/ms187928.aspx

You might also need to convert the '02/09/2015' to a datetime type using the same date format.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, correct spelling in your question title and body would help.

Try this..
insert into chequetmp 
select * 
from nopagados 
where convert (datetime,estimado)>= '20150209' and convert(datetime,estimado) <= '20150210'

Open in new window

One reason this wouldn't be working is that SQL 2000 had only the date data type (without the time), and versions after 2008 have date and time.  Note the change to 20150210.  See SQL Server Date Styles (formats) using CONVERT() for an excellent reference.

Also, 'INSERT INTO table SELECT *' is considered a poor programming practice, as it depends on both tables having the same columns and data types, and if that's not completely correct then this will generate an error.  Better to spell out every column.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Just for kicks and giggles, let's flush out any values in estimado that can't be converted to a date, so run this in 2012
SELECT estimado
FROM nopagados 
WHERE ISDATE(estimado) = 0

Open in new window

Or this will work in any SSMS
CREATE TABLE #tmp (val varchar(10)) 

INSERT INTO #tmp (val) VALUES ('01-01-2010'), ('05-24-2016'), ('12-25-0001'), ('banana') 
-- All values
SELECT * FROM #tmp
-- Values that can't be converted to a date
SELECT * FROM #tmp WHERE ISDATE(val) = 0

Open in new window

So if any rows are returned, then you're going to have to figure out what to do with these non-date values such that they can be converted to date format.

And just to 'check the box', dates should be in a date/datetime data type, and not a character data type.
0
ErnestoAuthor Commented:
i cant convert to 20150209 cos come from a varchar formated as / /
and 103 do not give me the correct range of dates
help!!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I don't have a SQL 2000 box in front of me, so can't test this, but instead of this

convert (datetime,estimado)

use this

CAST(estimado) as date
0
ErnestoAuthor Commented:
insert into chequetmp select * from nopagados where  cast (estimado as date)  >= '02/09/2015' and cast (estimado as date)  <= '05/09/2015'

jim
"Conversion failed when converting date and/or time from character string."

help me guys!! is my report engine!!
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Jim, One reason this wouldn't be working is that SQL 2000 had only the date data type (without the time), and versions after 2008 have date and time.   is wrong, it is exactly the other way round ;-).
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
edo60, why you can't use format 103? It is dd/mm/yyyy, and should work fine. You should use the convert for all the varchars representing dates. That is, if dd/mm/yyyy is the correct format used in the DB?
Insert into chequetmp
select * from nopagados
where convert(datetime,estimado,103) between convert(datetime, '02/09/2015', 103) and convert(datetime, '02/09/2015', 103)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnestoAuthor Commented:
the 103 it works, but do not give me the right query
0
Shaun KlineLead Software EngineerCommented:
If the original field, estimado, is a varchar field formatted as dd/mm/yyyy, have you tried just:
WHERE estimado = '02/09/2015'

?
0
ErnestoAuthor Commented:
do not work, i need a range of dates in the report, i dont know whats going on
help!!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> i need a range of dates in the report,
Please give us a before-and-after data mockup of what you're trying to pull off here, as I sense that the scope of the question has changed, and there's obviously a language barrier.
0
ErnestoAuthor Commented:
i want all the records of my database between a range of dates, in 2000 do it grate, but in 2014 fail

 (datetime,estimado)>= '02/08/2014' (datetime,estimado) <= '02/09/2015'

this is the original query

insert into chequetmp select * from nopagados where convert (datetime,estimado)>= '02/08/2014' and convert(datetime,estimado) <= '02/09/2015'

tsm
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If the table didn't change its types, the only way the results are different is because the locale is different, leading to a different datetime interpretation of your literals '02/08/2014' and '02/09/2015' as "8th of February 2014" and "9th of February 2015'.

Did you try my query in http:#a40958983?
0
ErnestoAuthor Commented:
quelmo!!!
done!!!
i do not see your query
tsm man!!!
0
ErnestoAuthor Commented:
tsm you guys
regards
qlmo your a #$%#$% genious!!!
0
ErnestoAuthor Commented:
regards!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.