TEMPORAL DATA PART V TEMPORAL DATA TYPES DATE

TEMPORAL DATA PART V

TEMPORAL DATA TYPES • DATE • • • TIMESTAMP • • Century Year Includes DATE Plus Fractional Seconds • Month 0 to 9 – default is 6 Day Hour Minute Second 2011 -04 -07 11. 07. 19 AM • 2011 -04 -07 11. 07. 19. 123456789 AM

TEMPORAL FUNCTIONS • TO_DATE(string [, format]) • TO_TIMESTAMP(string [, format]) • TO_CHAR(temporal_data [, format])

DATE AND TIME FORMATS

DATE AND TIME FORMATS

DATE AND TIME FORMATS

DATE AND TIME FORMATS

BACK TO THE FUNCTIONS SELECT to_char(sysdate, 'DD-MON-YYYY') FROM dual 18 -JUL-2019 SELECT to_date('25 -DEC-2019 15: 23', 'DD-MON-YYYY HH 24: MI: SS') FROM dual 12/25/2019 3: 25: 23 PM

MORE FUNCTIONS • ADD_MONTHS(date_value, months) • Adds months to date_value. Add negative months to subtract. SELECT add_months(sysdate, -2) FROM dual 5/18/2019 7: 28: 26 PM • CURRENT_DATE • Returns the current date and time as a DATE value SELECT current_date FROM dual 7/18/2019 7: 30: 18 PM

MORE FUNCTIONS • LAST_DAY(date_value) • Computes the last day of the month in which the given date_value falls. SELECT last_day(sysdate) FROM dual 7/31/2019 7: 35: 13 PM • MONTHS_BETWEEN(later_date, earlier_date) • Calculates the number of months between the two given dates. SELECT months_between(sysdate, to_date('15 -JAN-1968', 'DD-MON-YYYY')) FROM dual 618

MORE FUNCTIONS • NEXT_DAY(date, weekday) • Returns the date of the next specified weekday following the given date. SELECT next_day(sysdate, 'WED') FROM dual 7/24/2019 7: 40: 06 PM • SYSDATE • Returns the current date and time from the operating system on which the database resides as a DATE value SELECT sysdate FROM dual 7/18/2019 7: 41: 37 PM

DATE ADDITION • A number added to a date is assumed by the system to be a number sysdate of. SELECT days. FROM dual + 7 7/25/2019 7: 44: 36 PM • Convert what you want to add to days SELECT sysdate + (20/1440) • FROM Add 20 minutes to sysdate: dual 60 min/hr * 24 hours in a day = 1440 7/18/2019 8: 05: 28 PM

ROUNDING & TRUNCATING DATES • You can round or truncate a date to a specific minute, hour, day, week, quarter, year or century with ROUND or TRUNC respectively. SELECT round(sysdate, 'HH') FROM dual 7/18/2019 8: 00 PM SELECT trunc(sysdate, 'HH') FROM dual 7/15/2019 7: 00 PM

ROUNDING & TRUNCATING DATES

ROUNDING & TRUNCATING DATES

ROUNDING & TRUNCATING DATES SELECT * FROM sprhold WHERE trunc(sprhold_activity_date) = to_date('09 -APR-2019', 'DD-MON-YYYY')

QUESTIONS?