The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Question

0.00/5 (No votes)

See more:

SQL

alter function fn(@mdvn int, @fcdate datetime, @tcdate datetime)
returns @t1 table
(ANC int /*,TT1 int, TT2 int*/)
as
begin
declare @ANC int,@TT1 int,@TT2 int
declare @fdate datetime
declare @tdate datetime
set @fdate=convert(varchar(20),@fcdate,103)
set @tdate=convert(varchar(20),@tcdate,103)
declare ancur cursor for
select sum(ANC1) as ANC,SUM(TT1) as TT1,sum(TTB2) as TT2 from
(
select dvn_cd,
	case when visit_no=3 and Visit_date between @fdate and @tdate then 1 else 0 end as ANC1,
	case when TTB=1 and TTDate between @fdate and @tdate then 1 else 0 end as TT1,
	case when TTB>1 and TTDate between @fdate and @tdate then 1 else 0 end as TTB2 
from anvisits3 where dvn_cd=@mdvn and ANEDD between @fdate and @tdate
)a group by dvn_cd
open ancur
fetch next from ancur into @ANC,@TT1,@TT2
while @@fetch_status=0
begin
	insert into @t1
	select 'ANC'+convert(varchar(20),@ANC)+',TT1'+convert(varchar(20),@TT1)+',TT2'+convert(varchar(20),@TT2)
	fetch next from ancur into @ANC,@TT1,@TT2
end
return
end

---------------

SQL

select * from fn(41,'01/01/2011','2011/01/31')

Msg 242, Level 16, State 3, Line 1<br />
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

------------------
my query was executed successfully.. but i dint get my result

Posted 8-Jan-14 0:56am

Member 10463904

Updated 8-Jan-14 2:23am

Tejas Vaishnav

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Answer 1 · 2014-01-08T02:37:00

the problem is in your calling to the function...

SQL

select * from fn(41,'01/01/2011','2011/01/31')

date you have passed '01/01/2011' is successfully converted to datetime

SQL

SELECT convert(datetime,'01/01/2011')
--result is 
---2011-01-01 00:00:00.000

but the date '2011/01/31' is not able to converted to datetime

SQL

SELECT convert(datetime,'2011/01/31')
--result
--Msg 242, Level 16, State 3, Line 1
--The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

for getting resolve your issue your need to call your funcation like this...

SQL

select * from fn(41,'01/01/2011','01/31/2011')---MM/dd/yyyy format

to know more about datetime conversion and its formats please review the link..

http://www.sqlusa.com/bestpractices/datetimeconversion/[^]

Maciej Los · Answer 2 · 2014-01-08T02:38:00

Have a look at your code:

SQL

alter function fn(@mdvn int, @fcdate datetime, @tcdate datetime)
--input parameters are datetime data type
--...
--below 2 lines are unnecessary, because datetime is datetime and don't need to be converted to varchar!
--declare @fdate datetime
--declare @tdate datetime
--set @fdate=convert(varchar(20),@fcdate,103) -- error here!
--set @tdate=convert(varchar(20),@tcdate,103) -- error here!

Use input parameters!

SQL

select * from fn(41,'01/01/2011','2011/01/31')

Be consistent about passing parameters! Once it is mdy format, another time is ymd format.

See SET DATEFORMAT[^] documentation for further information.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0