SUBSTRING Usage in PLSQL-CodePudding

Hello I have a query in plsql. What I am looking for is that when input comes from the server as a parameter in the insert_date_time, I just want to take YYYYMMDD parts. For example: input 20221116105703603 what I look for is 20221116

I tried this query but couldnt succed I hope you can correct me, thank you.

Note: I defined insert_date_time as NUMBER(17) and in the java code it is defined as String.

SELECT insert_date_time FROM oc_vpos.payment_with_link a where a.insert_date_time = substr(:insert_date_time,1,8);

CodePudding user response：

if insert_date_time column's datatype is date (should be!), then you'll have to apply to_date function with appropriate format model (see line #10):

SQL> WITH
  2     payment_with_link (id, insert_date_time)
  3     AS
  4        (SELECT 1, DATE '2022-11-16' FROM DUAL
  5         UNION ALL
  6         SELECT 2, DATE '1998-10-23' FROM DUAL)
  7  SELECT insert_date_time
  8    FROM payment_with_link a
  9   WHERE a.insert_date_time =
 10         TO_DATE (SUBSTR ('&insert_date_time', 1, 8), 'yyyymmdd');
Enter value for insert_date_time: 20221116105703603

INSERT_DAT
----------
2022-11-16

SQL>

Otherwise, if that column's datatype is varchar2 (or similar char family data type), then your query should work if the first 8 digits of that column's values match what substr function returned.

CodePudding user response：

Don't store dates (and times) as strings or numbers, store them as DATE or, if you need fractional seconds, TIMESTAMP data types.

To convert it to a TIMESTAMP, you can use:

TO_TIMESTAMP(TO_CHAR(insert_date_time), 'YYYYMMDDHH24MISSFF3')

To convert it from a TIMESTAMP to a DATE at midnight, you can use:

TRUNC(TO_TIMESTAMP(TO_CHAR(insert_date_time), 'YYYYMMDDHH24MISSFF3'))

If you just want the first 8 characters then you can use:

SUBSTR(insert_date_time, 1, 8)

For example, given the sample data:

CREATE TABLE table_name (insert_date_time NUMBER(17));
INSERT INTO table_name (insert_date_time) VALUES (20221116105703603);

Then:

SELECT insert_date_time,
       TO_TIMESTAMP(TO_CHAR(insert_date_time), 'YYYYMMDDHH24MISSFF3') AS ts,
       TRUNC(TO_TIMESTAMP(TO_CHAR(insert_date_time), 'YYYYMMDDHH24MISSFF3')) AS dt,
       SUBSTR(insert_date_time, 1, 8) AS dt_string
FROM   table_name;

Outputs:

INSERT_DATE_TIME	TS	DT	DT_STRING
20221116105703603	2022-11-16 10:57:03.603000000	2022-11-16 00:00:00	20221116

fiddle

CodePudding user response：

It would be best to convert the column to a timestamp, or else you need a lot of conversion

CREATE TABLE payment_with_link (insert_date_time Number (17))

INSERT INTO payment_with_link VALUEs(20221116105703603)

1 rows affected

SELECT insert_date_time FROM payment_with_link a 
  where floor(a.insert_date_time/ 1000000000) = substr('20221116105703603',1,8);

INSERT_DATE_TIME
20221116105703603

fiddle