Date into Number

[#1]

Hi

How can i convert Date values into number format.

I used below fn but no luck. How can i convert.

NUM(date("DISTRIBUTED_DT",'YYYYMMDD'))

MY number should be YEARMONTHDATE e.g 20160817

[#2]

Above 2 condition giving same error

The following error occurred:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

[sunny_talwar]

Are you renaming the Distributed_DT to something else in your SELECT statement using as?

[#4]

[EXP_F_EXPENSE]:

LOAD*,

  Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;

SELECT "RECORD_DATE_KEY",

  "COMPANY_KEY",

  "ACFT_KEY",

  "AIRCRAFT_UNIT_KEY",

  "GL_ACCOUNT_KEY",

  "INV_KEY",

  "INV_LINE_ITEM_KEY",

  "FLEET_KEY",

  "LOCATION_KEY",

  "FACT_KEY",

  "ORD_LINE_ITEM_KEY",

  "SERVICE_LOCATION",

  "SUPPLIER_LOCATION",

  "ORD_KEY",

  "SUP_ITEM_KEY",

  "ATA_KEY",

  "SUP_KEY",

  "EXP_CLASS_CD",

  "EXP_DIVISION_CD",

  "EXP_COST_ROLLUP_CD",

  "EXP_COST_ACCOUNT_CD",

  "EXP_COST_SUBCATEGORY_CD",

  "INVOICED_RATE",

  "INVOICED_QUANTITY",

  "INVOICE_LINE_TOTAL",

  "DISPUTED_AMOUNT",

  "CLAIM_LINE_ITEM_SUM",

  "DEBIT_LINE_ITEM_SUM",

  "CREDIT_LINE_ITEM_SUM",

  "ACTUAL_EXPENSE",

  "ACCRUAL_EXPENSE",

  "GRAND_TOTAL_EXPENSE",

  "ACCRUAL_IND",

  //Num("DISTRIBUTED_DT") AS DATE_KEY,

    "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

The following error occurred:

Field not found - <DISTRIBUTED_DT>

[sunny_talwar · Accepted solution]

My friend, why are you not adding the DISTRIBUTED_DT in your SELECT statement? Unless you do, you will continue to get this error

[EXP_F_EXPENSE]:

LOAD*,

  Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;

SELECT "RECORD_DATE_KEY",

  "COMPANY_KEY",

  "ACFT_KEY",

  "AIRCRAFT_UNIT_KEY",

  "GL_ACCOUNT_KEY",

  "INV_KEY",

  "INV_LINE_ITEM_KEY",

  "FLEET_KEY",

  "LOCATION_KEY",

  "FACT_KEY",

  "ORD_LINE_ITEM_KEY",

  "SERVICE_LOCATION",

  "SUPPLIER_LOCATION",

  "ORD_KEY",

  "SUP_ITEM_KEY",

  "ATA_KEY",

  "SUP_KEY",

  "EXP_CLASS_CD",

  "EXP_DIVISION_CD",

  "EXP_COST_ROLLUP_CD",

  "EXP_COST_ACCOUNT_CD",

  "EXP_COST_SUBCATEGORY_CD",

  "INVOICED_RATE",

  "INVOICED_QUANTITY",

  "INVOICE_LINE_TOTAL",

  "DISPUTED_AMOUNT",

  "CLAIM_LINE_ITEM_SUM",

  "DEBIT_LINE_ITEM_SUM",

  "CREDIT_LINE_ITEM_SUM",

  "ACTUAL_EXPENSE",

  "ACCRUAL_EXPENSE",

  "GRAND_TOTAL_EXPENSE",

  "ACCRUAL_IND",

  //Num("DISTRIBUTED_DT") AS DATE_KEY,

   DISTRIBUTED_DT,

    "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

Please add the field in red above, else you will continue to get the error

[its_anandrjs]

You have to put this condition on he above of the

Ex:-

LOAD

Order,

Item,

Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD') as DateField;

SQL SELECT *

FROM <TableName>

Regards

Anand

[#7]

your correct.I will check result let you know.thanks a lot.

[sunny_talwar]

Yay!!! Sounds good