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

[narendiran]

Hi Govind,

Try this

=Num([DISTRIBUTED_DT])

[sunny_talwar]

May be this:

Num#(Date(DISTRIBUTED_DT, 'YYYYMMDD')) as YEARMONTHDATE

[#4]

	Select "DEBIT_LINE_ITEM_SUM",
	"CREDIT_LINE_ITEM_SUM",
	"ACTUAL_EXPENSE",
	"ACCRUAL_EXPENSE",
	"GRAND_TOTAL_EXPENSE",
	"ACCRUAL_IND",
	NUM#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS "DATE_KEY",

    "DISTRIBUTED_DT",

"APPROVED_DT"

FROM "EXP_F_EXPENSE";

Showing Error. I given like above.Please check

[#5]

The following error occurred:

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

[sunny_talwar]

The operation won't work in SQL Select statement. This will need to be carried out in a Load statement....

Load ....

TransformationHere;

Select

....

From ....

[#7]

Please give me steps.

My table

[EXP_F_EXPENSE]:

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_SK",

  "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#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY,

    "DISTRIBUTED_DT" AS DATE_KEY,

  "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

[sunny_talwar]

Try like this:

[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_SK",

  "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",

    "DISTRIBUTED_DT",

  "APPROVED_DT"

FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";

[its_anandrjs]

Try this way also

Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD')

Or

NUM( Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD') )

Regards

Anand

[#10]

The following error occurred:

Field not found - <DISTRIBUTED_DT>