16 Replies Latest reply: Aug 18, 2016 3:50 AM by Sunny Talwar RSS

    Date into Number

    Govind Ramchetty

      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

        • Re: Date into Number
          Narendiran Narayanan

          Hi Govind,

           

          Try this

           

          =Num([DISTRIBUTED_DT])

          • Re: Date into Number
            Sunny Talwar

            May be this:

             

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

              • Re: Date into Number
                Govind Ramchetty

                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

                • Re: Date into Number
                  Govind Ramchetty

                  The following error occurred:

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

                    • Re: Date into Number
                      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 ....

                        • Re: Date into Number
                          Govind Ramchetty

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

                            • Re: Date into Number
                              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";

                                • Re: Date into Number
                                  Govind Ramchetty

                                  The following error occurred:

                                  Field not found - <DISTRIBUTED_DT>

                                    • Re: Date into Number
                                      Sunny Talwar

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

                                       

                                      Capture.PNG

                                        • Re: Date into Number
                                          Govind Ramchetty

                                          [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>

                                            • Re: Date into Number
                                              Sunny Talwar

                                              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

                              • Re: Date into Number
                                Anand Chouhan

                                Try this way also

                                 

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

                                 

                                Or

                                 

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

                                 

                                 

                                Regards

                                Anand