SQL where condition

[#1]

Hello,

I'm running below script.

LOAD Distinct

Date(floor(LotStartDateTime),'YYYY-MM-DD hh:mm:ss.fff') as TempMaxDateTime,

Date(WeekEnd(LotStartDateTime),'DD-MM-YYYY') as WeekEndDate

Resident LatestWeek;

Let vWeekEndDate=Peek('WeekEndDate',-1);

Temp1:

LOAD Date(Max(TempMaxDateTime),'DD-MM-YYYY') as MaxDateTime

Resident Temp;

Let vMaxDate=Peek('MaxDateTime',-1);

LOAD *,

     LotStartDateTime AS TempLotStartDateTime

;

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

Day(LotStartDateTime) >= Day('$(vMaxDate)')

and

Day(LotStartDateTime) <= Day('$(vWeekEndDate)')

;

When i run the above query in qvw file, i'm getting the below error message as shown in second image.

In sql dates are like this

2011-06-08 00:10:12

2011-06-08 14:04:25

2011-06-09 11:00:04

[CELAMBARASAN]

Hi,

     Change this part

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

Day(LotStartDateTime) >= Day('$(vMaxDate)')

and

Day(LotStartDateTime) <= Day('$(vWeekEndDate)')

;

to

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

Day(LotStartDateTime) >= Day($(=Date(vMaxDate,'MM/DD/YYYY')))

and

Day(LotStartDateTime) <= Day($(=Date(vWeekEndDate,'MM/DD/YYYY'))

;

Celambarasan

[#3]

If i change now i'm getting the error as

[#4]

Sorry instead of Day i want to retrive the date only..

i'm poor in sql. please give a way to retrive the dates only

[#5]

I tried below code also but it is not working...

Let vWeekEndDate=Peek('WeekEndDate',-1);

Let vWeekEndDateDB=Date('$(vWeekEndDate)','DD/MM/YYYY');

Let vMaxDate=Peek('MaxDateTime',-1);

Let vMaxDateDB=Date('$(vMaxDate)','DD/MM/YYYY');

LOAD *,

          LotStartDateTime AS TempLotStartDateTime

;

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

CONVERT(nvarchar(10),LotStartDateTime,103) >= '$(vMaxDateDB)'

and 

CONVERT(nvarchar(10),LotStartDateTime,103) <= '$(vWeekEndDateDB)'

qvw format are like this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[sivarajs]

Can u try this...

from ....

where date(LotStartDateTime)=date(to_date('$(vMaxDate)','yyyy-mm-dd') )

Sivaraj S

[#7]

Still no use.

[sivarajs]

remove date() function just compare two dates like

where LotStartDateTime=to_date('$(vMaxDate)','yyyy-mm-dd')

[#9]

No this error:

[#10]

What i'm doing now is instead fo retriving less than weekend data. I'm trying to compare with particualar weeknumber. But i've seen that qlikview is reading the weeknumbers differently and sql reading weeknumbers differently.

For eg:

SELECT *

  FROM [Split Data].[dbo].[Week2]

  where

  DATENAME(WEEK,LotStartDateTime) = DATENAME(WEEK,'2011-06-27 00:00:00')

here week number is 27

It retrives the dates from 2011-06-26 to 2011-07-02

But in qlikview if you use the below code

Load * from

table1.qvd

where

Week(LotStartDateTime) = 27

It retrives the dates from 2011-06-27 to 2011-07-03

here week number is 27

This creates a problem for my incremental process for weekwise qvd files. How can i control this.