Company wont use calendar table and I'm not authorized too.

Also if getdate() - 8 days then Next week('2013-04-01 ~~ 2013-04-07)'

All the columns come from single table. But the values need to be fetched like next

week and currentweek.

So two conditions...

I want use case when statement where if the getdate is less than 15 days then fetch

'currentweek('the week's flag'.

eg : currentweek(2013-04-01 ~~ 2013-04-07).

Thanks for ur reply.

I want current_week column before fifteen days from now.

That is, getdate() - 15 days = current_week. But this column is not in date data type

so I used...

Thanks for ur reply.

But what update I should run and how to create dimension.

Also I ran the below query

select isdate(po_date)

from #mytable

where status = 'ontime'

select isdate(po_date)

from #mytable

where status = 'late'

for status "ontime" some of the rows returned 0

for status "late" all the...

Po_date to be converted to date format..

so that if the user enters the date in report then appropriate data should be fetched.

the date parameter in SSRS should be mapped to...

Actually I want the user to give values in runtime. Those two(@fromdate and @todate) are parameters.

For testing purpose I gave date ranges in literals.

its Jan 04 2012.MM/DD/YYYY format

So for Status "late" the query works fine.

For "ontime" the query throws conversion error.

The problem is with my UAT data?

if the query works fine with Status "late",why not with "ontime"?

pls find sample data...

CREATE TABLE #mytable

(

Location varchar(10),

...

Thank you very much.. this is what I expected.

But here comes another problem.That is the query works fine for "early" and "late" status.

For "ontime" its showing me conversion failed.

the problem...

Hi thanks for ur reply.

I tried somewhat like this.

SELECT LOCATION,SUM(SHIPQTY) LATE_QTY ,PO_DATE FROM mytable WHERE DROP_DATE_STATUS = 'LATE'

AND SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),1,2) + '/' + SUBSTRING(CAST(PO_DATE AS VARCHAR(11)),4,2)+'/'+...

I kept @odate as today() - 8days

and @Fromdate as "= Iif(Month(today())>04, "04/01/"&Year(Today()),Iif(Month(today())<= 04, "04/01/"&(Year(Today())-1),1))"

i wrote this query to fetch from april of last year.

i.e if i run today (year is...

The data looks like this.mm/dd/yyyy format but data type is nvarchar for this column

02/24/2012

01/20/2012

01/20/2012

01/20/2012

01/20/2012

01/20/2012

01/20/2012

01/27/2012

Thank you very much