Dates from Calendar control

Question

Post reply

Dates from Calendar control

Riskworks

Mr or Mrs. 500

Points: 592
More actions
August 20, 2010 at 10:21 am

#236847

This is an issue with the calendar control. The calendar control is not behaving inclusively.
So if the user selects June 1 to June 30, the query is missing a record with June 30 date and 3:00PM time.
No problem, just manipulate the end date in code? Any attempt to change the end date gives
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
I'm pretty good with dates and date manipulation, and I have tried to ensure that the date is a valid
date.
The control is seemly returning 6/30/2010 for the date (the date with no time component.)
OK no problem, set @End = dateadd(mi,59,dateadd(hh,11,@End) should get me to '20100630 11:59:00' right.
Is there a bug? Any suggestions?
John A. Byrnes

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

rogierpronkNL SSC Veteran Points: 232 More actions · Answer 1

I think you should convert the date+time from the source to a date instead of adding time to the parameters:

SELECT ModifiedDate

FROM Person.Address

WHERE (CONVERT(date, ModifiedDate) BETWEEN

@ReportParameter1 AND @ReportParameter2)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

The WHERE clause should be something like the following...

WHERE somedatecolumn >= startdatefromcalendarcontrol

AND somedatecolumn < DATEADD(dd,1,startdatefromcalendarcontrol)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Riskworks Mr or Mrs. 500 Points: 592 More actions · Answer 3

Interesting. When I convert to date the code does not throw the error, but it is still not picking up the June 30, 2010 3:00 row. When I convert to datetime it gives the out of range error.

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

Keep in mind, the date is coming from the control as a parameter, not the database. Nothing is being done to the variable, and the variable is a Date/Time type. This has to be a bug, otherwise I would expect the control to give '20100630 12:00' to the @End variable and I should be able to perform any normal date logic to it.

John A. Byrnes

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Riskworks (8/20/2010)
Interesting. When I convert to date the code does not throw the error, but it is still not picking up the June 30, 2010 3:00 row. When I convert to datetime it gives the out of range error.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Keep in mind, the date is coming from the control as a parameter, not the database. Nothing is being done to the variable, and the variable is a Date/Time type. This has to be a bug, otherwise I would expect the control to give '20100630 12:00' to the @End variable and I should be able to perform any normal date logic to it.

Ummm... Dunno the nuances of SSRS but the following works just fine in T-SQL...

SELECT CAST('June 30, 2010 3:00' AS DATETIME)

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

2010-06-30 03:00:00.000

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

veera.1255 SSC Enthusiast Points: 137 More actions · Answer 5

veera.1255

SSC Enthusiast

Points: 137

August 21, 2010 at 11:43 am

#1210053

HOW MANY ACCOUNTS CAN U CREATE IN SSRS

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

veera.1255 (8/21/2010)
HOW MANY ACCOUNTS CAN U CREATE IN SSRS

My recommendation is that you start a new thread for that question. More people will see it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

veera.1255 (8/21/2010)
HOW MANY ACCOUNTS CAN U CREATE IN SSRS

Yes for the new thread and how many accounts of what???

Riskworks Mr or Mrs. 500 Points: 592 More actions · Answer 8

OK I want to clarify to possibly help others.

The code would not allow me to manipulate the local variable which is being set by the calendar control, however it did allow me to manipulate the end date in the actual sql code that is using the local end variable.

So set @end = DATEADD(mi,-1,(dateadd(dd,1,@end)) gave an out of range error:

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

But doing this

Insert #tmp2 (pnid,pnn,anid,ann,nid,nn, pts, wts, cnt)

select t.pnid,t.pnn,t.anid,t.ann,t.nid,t.nn,

sum(c.points) as pts,sum(c.weight) as wts,

count(distinct c.sd) as cnt

from satCube c, #tbl t

where t.nid=c.nid

AND c.ePos = 1

and c.rd between @start and dateadd(hh,-1,dateadd(dd,1,@end))

group by t.pnid,t.pnn,t.anid,t.ann,t.nid,t.nn

Gave the correct result

HTH

John

John A. Byrnes

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

Riskworks (8/23/2010)
and c.rd between @start and dateadd(hh,-1,dateadd(dd,1,@end))

Gosh... that seems like "Death by SQL" because you're actually missing a whole hour in the day. Try this, instead...

and c.rd >= @Start and c.rd < DATEADD(dd,1,@End)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Riskworks Mr or Mrs. 500 Points: 592 More actions · Answer 10

Thanks Jeff. You solution is more elegent

Yep, you are right. I meant to subtract a mi or even ss which would work as well to get right up to '20100701'. What still baffles me is why the out-of-range error when I try to manipulate the date on the local variable. It is essentially the same code process, I would think.

Thanks again.

John

John A. Byrnes

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

Riskworks (8/26/2010)
Thanks Jeff. You solution is more elegent
Yep, you are right. I meant to subtract a mi or even ss which would work as well to get right up to '20100701'. What still baffles me is why the out-of-range error when I try to manipulate the date on the local variable. It is essentially the same code process, I would think.
Thanks again.
John

Not sure why you're getting the error on the calendar control. Which data type does it return?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 12

Hi

Jeff,

I came to forums after a long time and good to see you again.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

simon phoenix-479217 (8/31/2010)
Hi
Jeff,
I came to forums after a long time and good to see you again.

Hi Simon. My apologies... I wish I could remember who you are, but I don't. Would you refresh my ailing memory, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)