Skip to content

date.md

Latest commit

 

History

History
112 lines (89 loc) · 3.36 KB

date.md

File metadata and controls

112 lines (89 loc) · 3.36 KB

ROUND

  • hiểu là làm tròn lên theo đơn vị
  • Ex:
ROUND(TO_DATE ('22-AUG-03'),'YEAR')
Result: '01-JAN-04'

ROUND(TO_DATE ('22-AUG-03'),'Q')
Result: '01-OCT-03'

ROUND(TO_DATE ('22-AUG-03'),'MONTH')
Result: '01-SEP-03'

ROUND(TO_DATE ('22-AUG-03'),'DDD')
Result: '22-AUG-03'

ROUND(TO_DATE ('22-AUG-03'),'DAY')
Result: '24-AUG-03'

ADD_MONTHS

  • The syntax for the ADD_MONTHS function in Oracle/PLSQL is: ADD_MONTHS( date1, number_months )
  • Ví dụ:
    • ADD_MONTHS('01-Aug-03', 3): Result: '01-Nov-03'
    • ADD_MONTHS('01-Aug-03', -3): Result: '01-May-03'

MONTHS_BETWEEN

  • The syntax for the MONTHS_BETWEEN function in Oracle/PLSQL is: MONTHS_BETWEEN( date1, date2 )
  • The MONTHS_BETWEEN function returns a numeric value.
  • If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.
  • có trả về âm
  • date1 - date2

TRUNC (của date):

  • hiểu là làm tròn xuống theo đơn vị
  • The syntax for the TRUNC function in Oracle/PLSQL is: TRUNC ( date [, format ] )
  • Parameters or Arguments
    • date: The date to truncate.
    • format: Optional. The unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off. It can be one of the following values:
    • Valid format parameters
      • Year: SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
      • ISO Year: IYYY, IY, I
      • Quarter: Q
      • Month: MONTH, MON, MM, RM
      • Week: WW
      • IW: IW
      • W: W
      • Day: DDD, DD, J
      • Start day of the week: DAY, DY, D
      • Hour: HH, HH12, HH24
      • Minute: MI
  • Ví dụ:
TRUNC(TO_DATE('22-AUG-03'), 'YEAR'): Result: '01-JAN-03'
TRUNC(TO_DATE('22-AUG-03'), 'MONTH'): Result: '01-AUG-03'

EXTRACT:

  • The Oracle/PLSQL EXTRACT function extracts a value from a date or interval value.
  • Ex:
EXTRACT(YEAR FROM DATE '2003-08-22')
Result: 2003

EXTRACT(MONTH FROM DATE '2003-08-22')
Result: 8

EXTRACT(DAY FROM DATE '2003-08-22')
Result: 22

NEXT_DAY

  • The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date.
NEXT_DAY('01-Aug-03', 'TUESDAY')
Result: '05-Aug-03'

NEXT_DAY('06-Aug-03', 'WEDNESDAY')
Result: '13-Aug-03'

NEXT_DAY('06-Aug-03', 'SUNDAY')
Result: '10-Aug-03'

LAST_DAY

  • The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a date value.
LAST_DAY(TO_DATE('2003/03/15', 'yyyy/mm/dd'))
Result: Mar 31, 2003

LAST_DAY(TO_DATE('2003/02/03', 'yyyy/mm/dd'))
Result: Feb 28, 2003

LAST_DAY(TO_DATE('2004/02/03', 'yyyy/mm/dd'))
Result: Feb 29, 2004

Other

  • A TIMESTAMP WITH LOCAL TIMEZONE data type column in Oracle is stored in the database using the time zone of the database session that inserted the row, not the time zone of the session.
    • The CURRENT_TIMESTAMP function returns data with time zone information.
  • sysdate: current date của server cài db
  • current_date: current date của user login
  • sessiontimezone: timezone của user session
  • systimestamp: date time của db
  • current_timestamp: current time của user session
  • LOCALTIMESTAMP: The Oracle/PLSQL LOCALTIMESTAMP function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP value.