Day & Month & Year

[#1]

Hello all,

I've attached snapshot for order table and business intelligence plan. According to business intelligence plan how can we bring the data(Year & Month & Year) from orders date? 

I request to help in this if anyone knows.

Regards,

Sattish G

[#2 · Accepted solution]

LOAD Day(OrderDate) AS Day,

     Month(OrderDate) AS Month,

     Year(OrderDate) AS Year,

     Date(OrderDate,'DDMMYYYY') AS Date_DDMMYYYY_Format
FROM Orders;

If you need Year Month Year use Year(OrderDate)&Month(OrderDate)&Year(OrderDate)

[anbu1984]

Month(OrdersDate) gives you month in MMM. If you want months in number then use Num(Month(OrdersDate))

Date#(OrdersDate,'MMM-YYYY')

[ashfaq_haseeb]

Hi

Try the below.

Orders:

LOAD       

    CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    Year(OrderDate) AS Year,

    Month(OrderDate) AS Month,

    Day(OrderDate) AS Day,

    OrderID,

     ShipperID

;

SQL SELECT * FROM Orders;

Hope it help

Regards

ASHFAQ

[tresesco]

If your source data is date type, you can directly use qv functions Day(), month() and year() like:

Load

          Year(OrderDate) as Year,

          Month(OrderDate) as Month,

          Day(OrderDate) as Day,

          WeekDay(OrderDate) as WeekDay

From <>;

Otherwise, use parsing function to get it in date format like:

Load

          Year(Date#(OrderDate, 'DD-MMM-YY') as Year,

          Month(Date#(OrderDate, 'DD-MMM-YY') as Month,

          WeekDay(Date#(OrderDate, 'DD-MMM-YY') as WeekDay

From <>;

[#6]

Thanks for answering.

And i would like to know about ODBC & OLEDB(datasources) definitions and as well difference.

How it works in real time?

When we go for ODBC and when we go for OLEDB?

Please explain in detail if anyone knows.

[#7]

It depends on datasource, generally OLDEB quicker to extract but it is not always optimal.  The data is realtime (at the time you reload the document) unless you are using direct discovery

[MK_QSL]

It's always better to use Master Calender for filling Dates and getting Year, Month, Quarter, YearMonth fields.

Check enclosed file ...

[jagan]

Hi,

Check this

• ODBC provides access only to relational databases
• OLE DB provides the following features
  • Access to data regardless of its format or location
  • Full access to ODBC data sources and ODBC drivers

oledb - what is the difference between OLE DB and ODBC data sources? - Stack Overflow

http://database.ittoolbox.com/documents/odbc-vs-oledb-18150

Regards,

Jagan.

[#10]

Hi,

Everything is OK.But i can't get format of YearMonth field according to Business Intelligence Plan.Please help me in this.