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
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
With properly storead date or timestamp datatypes, you can’t separate date and time in Oracle. The
date
andtimestamp
datatypes will always have both components. Use theto_char
command to control the format used to display that data when you select it.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:
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.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…