skip to Main Content

I have a DataTable column which has values like 01-04-2023 00:00:00. I want only 04-2023 from that column and update in new DataTable. How can I split that in DataTable?

Dt has that column with name DATA_WITH_MONTH_YEAR.

update

Below is the image of datatable columns

enter image description here

Below is the query:

INSERT INTO TBL_IPCOLO_BILLING_MST (
    POLITICAL_STATE_NAME,
    POLITICAL_STATE_CODE,
    RFCDATE,
    RFS_DATE,
    RFE1_DATE, 
    SITE_DROP_DATE,
    IP_COLO_SITEID,
    MW_INSTALLED,
    DG_NONDG,
    EB_NONEB,                                        
    ID_OD_COUNTCHANGE,
    ID_OD_CHANGEDDATE,
    RRH_COUNTCHANGE,
    RRH_CHANGEDDATE,
    TENANCY_COUNTCHANGE,
    TENANCY_CHANGEDDATE,                                        
    SAP_ID,
    CREATED_BY,
    RFS_DATE_5G,
    DROP_DATE_5G,
    OLT_COUNT,
    OLT_CHANGE_DATE,
    DIESEL_DOWNTIME_MINUTES,
    OVERALL_INFRA_OUTAGE_MINUTES,
    DIESEL_DOWNTIME_MIN_MY,
    OVERALL_INFRA_OUTAGE_MIN_MY) 

VALUES 
    (:POLITICAL_STATE_NAME,
    :POLITICAL_STATE_CODE,
    :RFCDATE,
    :RFS_DATE,
    :RFE1_DATE,
    :SITE_DROP_DATE,
    :IP_COLO_SITEID,
    :MW_INSTALLED,
    :DG_NONDG,
    :EB_NONEB,
    :ID_OD_COUNTCHANGE,
    :ID_OD_CHANGEDDATE,
    :RRH_COUNTCHANGE,
    :RRH_CHANGEDDATE,
    :TENANCY_COUNTCHANGE,
    :TENANCY_CHANGEDDATE,                                        
    :SAP_ID,
    :CREATED_BY,
    :RFS_DATE_5G,
    :DROP_DATE_5G,
    :OLT_COUNT,
    :OLT_CHANGE_DATE,
    :DIESEL_DOWNTIME_MINUTES,
    :OVERALL_INFRA_OUTAGE_MINUTES,
    :TO_CHAR(DIESEL_DOWNTIME_MINUTES_MON_YEAR, 'MM:YYYY') AS DIESEL_DOWNTIME_MINUTES_MON_YEAR,
    :OVERALL_INFRA_OUTAGE_MINUTES_MON_YEAR
    )

2

Answers


  1. With properly storead date or timestamp datatypes, you can’t separate date and time in Oracle. The date and timestamp datatypes will always have both components. Use the to_char command to control the format used to display that data when you select it.

    select 
        to_char(my_date_column, 'MM-YYYY') "MyDateColumn" 
    from my_table;
    

    With – as in your case – date data improperly stored as text, you must convert it first to a date before you can convert it back to text in the desired format:

    select 
        to_char(
            to_date(my_date_column,'MM-DD-YYYY HH24:MI:SS')
        , 'MM-YYYY') "MyDateColumn" 
    from my_table;
    

    Note that any variation in the format of the "date" in its text column would blow up your query, as the input will not match the format filter in the to_date function call. My advice – if you have the opportunity – would be to make these proper date or timestamp columns and store the data correctly in the first place. Sooner or later, storing dates as strings without input validation will result in data corruption and you will have to do it anyway.

    Login or Signup to reply.
  2. So forget what I said about date storage: storing dates as text as a very, very poor design decision because you can’t do anything with them until you convert the datatype. If there’s any inconsistency in formatting how the text was stored, it will blow up any query doing the conversion. Updating my answer…

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search