How to get data between and including date range

[sekar211]

Hi,

I have created a prompt which asks for date range FROM and TO.The date Data type is TimeStamp and the database is SQLserver.
Example:The report gives values for 7/10/2004 when the range is FROM=7/9/2004 and TO:7/11/2004.
But when I give range as FROM:7/10/2004 and TO:7/10/2004,it doesn't return any value for 7/10/2004!

If any one of you know what the problem might be please let me know.

Thank you

 

[JaC74]

You need to make the qualifer >= and <=. Or use 'Between'

By doing so, you will include the start and end date range.

 

[sekar211]

I tried 'Between', >= and <= operator but it's notincluding end date in the qualification.Since the data type is timestamp,do u think i have to make any changes to the datatype?

 

[JaC74]

Unless you need your date attribute to be timestamp, you should convert it in to Date format. What does the 'Where' clause look like?

 

[sekar211]

I changed the Datatype to Date and still same thing.
Below is the SQL for same dates;

Select a12.fldTrackingNum fldTrackingNum,
count(distinct a12.fldTrackingNum) WJXBFS1
into #ZZT3C080G5HSP001
from uv_PayAdjust a12
join uv_AdjustApproval a13
on (a12.fldApprovalId = a13.fldApprovalId)
where (a13.fldApprovalStartDate >= '2004-07-09'
and a13.fldApprovalStartDate <= '2004-07-09')
group by a12.fldTrackingNum

Thank You,
Puneeth

 

[JaC74]

You need to qualify on both startdate and enddate.

where (a13.fldApprovalStartDate >= '2004-07-09'
and a13.fldApprovalStartDate <= '2004-07-09')

should be...

where (a13.fldApprovalStartDate >= '2004-07-09'
and a13.fldApprovalEndDate <= '2004-07-09')

 

[sekar211]

I have only one column in the table called "fldApprovalStartDate" and the end user wants to see the records from a particular approval start date to another particular start date.
Thank you

 

[entaroadun]

Keep in mind that the datetime data type in SQL includes time, which defaults to midnight if not specified.


where (a13.fldApprovalStartDate >= '2004-07-09'
and a13.fldApprovalStartDate <= '2004-07-09')

becomes

where (a13.fldApprovalStartDate >= '2004-07-09 12:00:00 AM'
and a13.fldApprovalStartDate <= '2004-07-09 12:00:00 AM')

which misses

'2004-07-09 01:00:00 AM'


what you want is

where (a13.fldApprovalStartDate >= '2004-07-09'
and a13.fldApprovalStartDate < '2004-07-10')

note the <= became <

this will give you all points in time after midnight 7/9 but before midnight 7/10, which is effectively the entire day of 7/9.


either educate the users of your prompt (very hard) or use an apply statement to add a day to your TO argument.

 

[sekar211]

I am trying to add a day to the TO argument as you mentioned.I am trying this:

Operator : Between
SimplePrompt:Enter From date
and
Custom:Applysimple"dateadd,day,1,#0)",fldApprovalStartDate)in the filter ID qualification of the date.

Since fldapprovalstartdate has to be prompt I tried to drag and drop the prompt in it's position in the above definition but it is not allowing!

Am I doing in the right way?

 

[entaroadun]

Try this:

Create a simple prompt for the FROM date.
Create a simple prompt for the TO date.

Create a filter using a custom expression:

ApplyComparison("(#0 >= #1 And #0 < #2)",[Approval Start Date Attribute],[From Simple Prompt],[To Simple Prompt])

 

[sekar211]

I tried Custom:Applysimple("dateadd(day,1,#0)",?[fldApprovalStartDate]) and it is working fine now.

Thanks a lot for your hint.