How to add oracle TRUNC(SYSDATE-100,'Q') to MySQL

0
votes

I have MySQL installed and I have an oracle script with me and I create schemas and tables and stuck with trunc and quarter in oracle and want them to insert in MySQL.

how to use trunc and q while inserting into mysql? 

INSERT INTO ASSIGNMENT (ASSIGNMENT_ID, CONSULTANT_ID,CLIENT_ID,START_DATE,END_DATE,PAY,COMMENTS) 
  VALUES (1, 2, 1, date_format(quarter(SYSDATE()-100),'%d-%b-%Y'),date_format(quarter(SYSDATE()-100),'%d-%b-%Y')+31,500, null);

date_format(quarter(SYSDATE()-100),'%d-%b-%Y'),date_format(quarter(SYSDATE()-100),'%d-%b-%Y')+31,500

Error Code: 1292. Incorrect datetime value: '4' Error Code: 1292. Incorrect date value: 'Q' for column 'start_date' at row 1 Error Code: 1292. Incorrect date value: 'Q' for column 'start_date' at row 1

1
mysqloracleoracle11gdatetime-format
SELECT QUARTER(SYSDAte()-100); returns 4, so that is no chance to form it like this to a date with '%d-%b-%Y', - nbk

1 Answers

0
votes

You can use :

set @former_day = date_add( sysdate(), interval -100 day );

select
date_format(@former_day ,'%Y-01-01') + interval quarter(@former_day)-1 quarter 
as new_date

to generate what you want.

  • to get the beginning of the current year for 100 days before :

    date_format(@former_day ,'%Y-01-01')

  • to add the number of quarters to the beginning of the quarter for 100 days before :

    + interval quarter(@former_day)-1 quarter

Demo