I need to write script for a date interval for survey date

[trishita]

I need to add two dates for a specific survey date range, 2 days before the survey and  seven days after it.

LOAD SURVEY_ID,

    SURVEY_ID as SURVEY_ID_General_Information,

    num(SURVEY_DATE)         as SURVEY_DATE_NUM,

    SURVEY_DATE,

   SURVEY_DATE -2   as SURVEY START DATE,

    SURVEY_DATE +7   as SURVEY END DATE,

    IMO_NO,

    Upper (SHIP_NAME)         as SHIP_NAME,

    OWNER,

    KIND             as SURVEY_TYPE,

    VOYAGE_NO

FROM

[\\ww.hl.lan\HH1\Groups\RHH0T432 - ROB project\14 - QlikView\COMPASS BIAC\RoB - QDF structure 01\QlikViewStorage\SourceDocuments\10.Operations\3.Remaining On Board\2.QVD\mo_bun_sur_survey_view.qvd]

(qvd);

But I am currently having error.Can someone help me with my syntax

[tresesco]

Try like:

Date(SURVEY_DATE -2)  as [SURVEY START DATE],

Date(SURVEY_DATE +7)  as [SURVEY END DATE],

[Anil_Babu_Samineni]

Can you show error?

[trishita]

i tried it this way but i want  to have the date format in the way 'YYYY-MM-DD' BUT its showing dd.mm.yyyy format.

i want to have it in YYYY-MM-DD' format

[tresesco]

I am assuming that you wanted to reply and mark on my reply. You can use the desired format in date(), like:

Date(SURVEY_DATE -2, 'YYYY-MM-DD')  as [SURVEY START DATE],

Date(SURVEY_DATE +7, 'YYYY-MM-DD')  as [SURVEY END DATE],

[ahmar811 · Accepted solution]

first, you have to check if your date is in number format/Date or String.

if it is not in number format you first have to convert into the number using Date#() function and use tresesco b‌ expression like below :

suppose your date format in the string like this "22 Apr 2018"

use expression:

Date(Date#(SURVEY_DATE,'DD MMM YYYY')-2) as [SURVEY START DATE],

Date(Date#(SURVEY_DATE,'DD MMM YYYY')+7) as [SURVEY END DATE],

if you have date in Number/Date format use below

Date(SURVEY_DATE-2,'DD MMM YYYY') as [SURVEY START DATE],

Date(SURVEY_DATE+7,'DD MMM YYYY') as [SURVEY END DATE],

I hope this will help youj

Regards

Ahmar