1 / 28

CS 3630 Database Design and Implementation

Learn about the various date formats in Oracle, including default formats and other formats like day, month, year, hour, minute, and second. Explore functions like TO_DATE and TO_CHAR, and how to compare dates in SQL queries.

lizette
Download Presentation

CS 3630 Database Design and Implementation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 3630 Database Design and Implementation

  2. Oracle Date Formats • Default Date Format dd-Mon-yy -- 30-Mar-15

  3. Oracle Date Formats Other Date Format Day ddd: day of the year dd : 01-31 d : day of the week: Sun (1), Mon (2), ... dy : mon, tue, .. (all lower case) Dy : Mon, Tue (Init cap) DY : MON, TUE (all caps)

  4. Oracle Date Formats Other Date Format Month Month: name of month (char(9)) Mon : Three chars mm : 01 - 12 Year Year: Year spelled out: Two Thousand Fifteen yyyy: 4 digits yyy: 3 digits yy: 2 digits y: 1 digit

  5. Oracle Date Formats Other Date Format Hour HH (HH12): 01 - 12 HH24 : 00 - 23 HH AM : with AM/PM Minute MI : 00 - 59 Second SS : 00 - 59 Time is included in date Default values: 00:00:00 (HH24)

  6. Oracle Function To_Date insert into Booking values ('H01', 'G01003', '25-Apr-2006', to_date('14-May-2006 9:20:33', 'dd-Mon-yyyy hh:mi:ss'), 'R001'); -- Not needed if in standard format

  7. Oracle Function To_Char Select to_char(date_to, 'Mon-dd-yyyy hh:mi:ss') from booking;

  8. Oracle Function To_Char -- Will display Apr-25-2004 Select guest_no, To_Char(Date_from, 'Mon-dd-yyyy') From booking; -- Will display 04-25-2004 select to_char(date_from, 'mm/dd/yyyy'), to_char(date_to, 'mm/dd/yyyy') from booking; -- Will display Apr 25 2004 Select guest_no, To_Char(Date_from, 'Mon dd yyyy') From booking;

  9. Date or String? col "Guest No" format a9 col "Date From" format a15 Select guest_no "Guest No", To_Char(Date_from, 'Mon-dd-yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" From booking Order by "Date From" desc; pause select guest_no "Guest No", to_char(date_from, 'mm-dd-yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" from booking Order by "Date From" desc; pause Select guest_no "Guest No", To_Char(Date_from, 'Mon dd yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" From booking Order by "Date From" desc;

  10. Date or String? Select guest_no "Guest No", To_Char(Date_from, 'Mon-dd-yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" From booking Order by Date_from desc; pause select guest_no "Guest No", to_char(date_from, 'mm-dd-yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" from booking Order by Date_from desc; pause Select guest_no "Guest No", To_Char(Date_from, 'Mon dd yyyy') "Date From", to_char(date_to, 'mm-dd-yyyy') "Date To" From booking Order by Date_from desc;

  11. Comparing Dates Select guest_no "Guest No", To_Char(Date_from, 'Mon-dd-yyyy') "Date From" From booking Where Date_From >= To_Date('05-May-2005'); pause Select guest_no "Guest No", To_Char(Date_from, 'Mon-dd-yyyy') "Date From" From booking Where Date_From >= '05-May-2005'; pause Select guest_no "Guest No", To_Char(Date_from, 'Mon-dd-yyyy') "Date From" From booking Where Date_From between To_Date('12-Apr-2005') and To_Date('30-Apr-2005');

  12. Table Dual SQL> Desc Dual Name Null? Type ----------------------------------------- -------- -------------- DUMMY VARCHAR2(1) SQL> Select * from dual; DUMMY --------- X

  13. Function SysDate • How to display date/time in SQLPlus? Select SysDate From Dual; Select to_char(SysDate, 'Mon-dd-yyyy hh:mi:ss') From Dual;

  14. Select Clause Select SysDate From Booking; Select 'The system date function', SysDate From Booking Select Hotel_No, SysDate From Booking Where Hotel_No = 'H01';

  15. Date Functions -- next day Select sysdate + 1 from dual; -- previous month select add_months(sysdate, -1) from dual; -- last_day: last day of the month select last_day(sysdate) from dual; -- next_day: the next Wednesday select next_day(sysDate, 'Wednesday') From dual; -- Months_Between select months_between(sysdate, add_months(sysdate, 3)) from dual;

  16. Date Functions -- last month of the year select add_months(sysdate, 12 - to_char(sysdate, 'mm')) from dual; -- to_number select add_months(sysdate, 12 - to_Number(to_char(sysdate, 'mm'))) from dual; -- last day of the year select last_day( add_months(sysdate, 12 - to_char(sysdate, 'mm')) ) From dual; -- first month of the year Select add_months(sysdate, 1 - to_char(sysdate, 'mm')) from dual; -- first day of the year -- need to be on the same line Select add_months(sysdate, 1 - to_char(sysdate, 'mm')) - to_char(add_months(sysdate, 1 - to_char(sysdate, 'mm')), 'dd') + 1 from dual;

  17. Date and String The latest (largest) Date_To Select Max(Date_To) From Booking; Select To_Char(Max(Date_To), 'dd Mon yyyy') From Booking; -- Incorrect! Select Max(To_char(Date_To, 'dd Mon yyyy')) From Booking;

  18. Comparing Dates Bookings during April 2005 Select hotel_no, guest_no, to_char(date_from, 'dd-mon-yyyy'), to_char(date_to, 'dd-mon-yyyy'), room_no From Booking Where Date_from between '1-Apr-05' and '30-Apr-05' Or Date_to between '1-Apr-05' and '30-Apr-05'; -- Correct?

  19. Comparing Dates Bookings during April 2005 Select * From Booking Where Date_from between '1-Apr-05' and '30-Apr-05' Or Date_to between '1-Apr-05' and '30-Apr-05' Or Date_from < '1-Apr-05' and Date_To > '30-Apr-05'; -- bookings started before apr 2005 and -- ended after apr 2005

  20. Comparing Dates Bookings during April 2005 (better solution!) Select * From Booking Where not Date_from > '30-Apr-05' and not Date_to < '1-Apr-05';

  21. Comparing Dates Bookings during April 2005 Select * From Booking Where Date_from <= '30-Apr-05' and Date_to >= '1-Apr-05' Order by Date_from; -- Same as -- Where not Date_from > '30-Apr-05' -- and not Date_to < '1-Apr-05';

  22. Comparing Dates Bookings during April 2005 Select * From Booking Where not (Date_from > '30-Apr-05' Or Date_to < '1-Apr-05'); -- DeMorgan’s Law Select * From Booking Where not Date_from > '30-Apr-05' and not Date_to < '1-Apr-05';

  23. Comparing Dates Bookings this year Select * From Booking Where To_char(Date_from, 'yyyy') = To_Char(SysDate, 'yyyy') Or To_Char(Date_to, 'yyyy') = To_Char(SysDate, 'yyyy'); -- Assuming no bookings are more than one year -- Assuming bookings could be longer than one year Select * From Booking Where To_char(Date_from, 'yyyy') = To_Char(SysDate, 'yyyy') Or To_Char(Date_to, 'yyyy') = To_Char(SysDate, 'yyyy') or to_number(To_char(Date_from, 'yyyy')) < to_number(To_Char(SysDate, 'yyyy')) and to_number(To_Char(Date_to, 'yyyy')) > to_number(To_Char(SysDate, 'yyyy'));

  24. Bookings of the current month(of the current year) -- Bookings could be longer than one month Select * From Booking Where Date_From <= Last_Day(SysDate) and Date_To >= (SysDate - To_Number(To_Char(SysDate, 'dd')) + 1);

  25. Bookings of the current month(of the current year) -- Bookings could be longer than one month Select * From Booking Where Date_From <= Last_Day(SysDate) and Date_To > Last_Day(Add_Months(SysDate, -1));

  26. Bookings of the previous/next month -- Bookings could be more than one month Select * From Booking Where Date_From <= Last_Day(Add_Months(SysDate, -1)) and Date_To >= ((Add_Months(SysDate, -1)) - To_Number(To_Char(Add_Months(SysDate, -1), 'dd')) + 1);

  27. Bookings of the previous/next month -- Bookings could be more than one month Select * From Booking Where Date_From <= Last_Day(Add_Months(SysDate, -1)) and Date_To > Last_Day(Add_Months(SysDate, -2));

  28. No Commit for queries (UserName_A8)!

More Related