How to enter the date while loading from a table?

[#1]

Hi All,

I am extracting the data from an Excel sheet that does not contain the date.

But that date needs to store in the QVD as a column along with the excel sheet data.

My data is as follows...

ID               Department               Stage               Month_Date

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

123              Abc                         Stage1              31/01/2013

111               Xyz                         Stage 2            31/01/2013

I extracte the data as follows

LOAD ID,

          Department,

          Stage,         
          31/01/2013 as Month_Date

From  Dummy.xlsx (ooxml, embedded labels);

But QV is taking the Month_Date field as Number, after changing that number to Date format using "Override Document Settings" in the properties of the list box, It is showing that

[Anonymous · Accepted solution]

To be on the safe side you can do:

LOAD ID,

          Department,

          Stage,         
          date(date#('31/01/2013', 'DD/MM/YYYY'),'DD/MM/YYYY') as Month_Date

From  Dummy.xlsx (ooxml, embedded labels);

If you just put 31/01/2013 I think it will be evaluated as 31 divided by 1 divided by 2013.

[somenathroy]

Use Date(Date#('31/01/2013'.'DD/MM/YYYY'),'DD/MM/YYYY') as as Month_Date in LOAD statement.

Regards,

Som

[Anonymous]

Hi ,

USE THIS

Load

ID,

Department,

Stage

Date(today(),'DD/MM/YYYY') as Date

From  Dummy.xlsx (ooxml, embedded labels);

[VishalWaghole]

Please use below script :-

LOAD ID,

          Department,

          Stage,         
          date(Month_Date,'DD-MM-YYYY') as Month_Date

From  Dummy.xlsx (ooxml, embedded labels);

Thanks,

Vishal

[nirav_bhimani]

Hi,

You want 01 as month then try this function

Month(DATE#('31/01/2013' , 'DD/MM/YYYY') )

Hope it will resolve ur problem.

Regards,

Nirav Bhimani

[kaushiknsolanki]

Hi,

     Try this.

     LOAD ID,

          Department,

          Stage,         
          Date(Makedate(2013,01,31)) as Month_Date

     From  Dummy.xlsx (ooxml, embedded labels);

Regards,

Kaushik Solanki

[#8]

Thanks Som & Johannes...