1: | From where is the system date and time normally derived? |
A1: | The system date is derived from the current date and time of the operating system on the host machine. |
2: | List the standard internal elements of a DATETIME value. |
A2: | YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. |
3: | What could be a major factor concerning the representation and comparison of date and time values if your company is an international organization? |
A3: | The awareness of time zones may be a concern. |
4: | Can a character string date value be compared to a date value defined as a valid DATETIME data type? |
A4: | A DATETIME data type cannot be accurately compared to a date value defined as a character string. The character string must first be converted to the DATETIME data type. |
Provide SQL code for the exercises given the following information:
Use SYSDATE to represent the current date and time.
Use the table called DATES.
Use the TO_CHAR function to convert dates to character strings with the following syntax:
TO_CHAR('EXPRESSION','DATE_PICTURE')
Use the TO_DATE function to convert character strings to dates with the following syntax:
TO_DATE('EXPRESSION','DATE_PICTURE')
Date picture information:
DATE PICTURE | MEANING |
---|---|
MONTH | Month spelled out |
DAY | Day spelled out |
DD | Day of month, number |
MM | Month of year, number |
YY | Two-digit year |
YYYY | Four-digit year |
MI | Minutes of the hour |
SS | Seconds of the minute |
1: | Use Assuming today is 1999-12-31, convert the current date to the format December 31 1999. |
A1: | SELECT TO_CHAR(SYSDATE,'MONTH DD YYYY') FROM DATES; |
2: | Use Convert the following string to DATE format:'DECEMBER 31 1999' |
A2: | SELECT TO_DATE('DECEMBER 31 1999','MONTH DD YYYY') FROM DATES; |
3: | Use Write the code to return the day of the week on which New Year's Eve of 1999 falls. Assume that the date is stored in the format 31-DEC-99, which is a valid DATETIME data type. |
A3: | SELECT TO_CHAR('31-DEC-99','DAY') FROM DATES; |