Chapter 9 – Date Functions
“Whoever gossips to you will gossip of you.”
- Spanish Proverb
Current_Date
The Current_Date will return today's date.
Current_Date and Current_Time
Above are the keywords you can utilize to get the date and the time. These are reserved words that the system will deliver to you when requested.
Current_Date and Current_Timestamp
Above are the keywords you can utilize to get the date and timestamp. These are reserved words that the system will deliver to you when requested.
Current_Timestamp with Milliseconds
The CURRENT_TIMESTAMP has the ability to put in milliseconds.
Current_Timestamp with Microseconds
The CURRENT_TIMESTAMP has the ability to put in microseconds.
Current_Timestamp and SYSDATE are Synonyms
The CURRENT_TIMESTAMP and the SYSDATE are synonyms of each other.
The Now Function
You can get the timestamp using the NOW function.
Adding Days, Weeks and Months to a Date
The example above shows you how to add days, a week and three months to a date.
Add or Subtract Days from a date
SELECT Order_Date
,Order_Date + 60 as "Due Date"
,Order_Total
,Order_Date + 50 as Discount
,Cast(Order_Total *.98 as Decimal(10,2)) as Dis_Total
FROM Order_Table
ORDER BY 1 ;
When you add or subtract from a Date you are adding/subtracting Days
In the query above, we are adding 60 days to the Order_Date and adding 50 days for a discount payment.
The EXTRACT Command
This is the Extract command. It returns a portion of a date, time or timestamp.
EXTRACT from DATES and TIME
SELECT Current_Date
,EXTRACT(Year from Current_Date) as Yr
,EXTRACT(Month from Current_Date) as Mo
,EXTRACT(Day from Current_Date) as Da
,Current_Time
,EXTRACT(Hour from Current_Time) as Hr
,EXTRACT(Minute from Current_Time) as Mn
,EXTRACT(Second from Current_Time) as Sc
,EXTRACT(TIMEZONE_HOUR from Current_Time) as Th
,EXTRACT(TimeZONE_MINUTE from Current_Time) as Tm ;
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
EXTRACT of the Month on Aggregate Queries
The above SELECT uses the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY.
Deriving a Timestamp from a Date and Time
You can use the technique above to derive a Timestamp from a date and time column. Above, we used the Current_Time, but you can utilize an actual time column instead.
Formatting Dates and Dollar Amounts
SELECT
Order_Date
,To_Char(Order_Date, 'Day, DD MON-YYYY') AS "Spelled"
,To_Char(Order_Date, 'Mon DD, YYYY') as "Abbrev_Month"
,To_char(Order_Total, '$99,999.99') As Order_Total
FROM Order_Table
ORDER BY 1 ;
The TO_CHAR command takes a value and convert it to a character string. You can use this to format dates and dollar amounts.
TO_CHAR Example that is Impressive
The TO_CHAR command takes a value and converts it to a character string. Check out the example above. Impressive!
TO_CHAR Example that is Amazing
The TO_CHAR command takes a value and converts it to a character string. Check out the example above. Amazing!
TO_CHAR Example to get Seconds since Midnight
The TO_CHAR command takes a value and converts it to a character string. Check out the example above.
TO_CHAR Example that is ahead of its Time
The TO_CHAR command takes a value and converts it to a character string. Check out the example above. It is 10 years ahead of its time!
TO_DATE
The TO_DATE function converts a string that is in a given format to a DATE data type. It can also accept a number instead of a string. You can specify a literal string, a literal number, or a column that contains a string or a number. The format must correspond to a supplied format-string. Only if the string is in the format ‘DD-MON-YY’ can the format-string be left out.
TO_TIME
The TO_TIME function converts a string that is in a given format to a TIME data type. It can also accept a number instead of a string. You can specify a literal string, a literal number, or a column that contains a string or a number. The format must correspond to a supplied format-string. Only if the string is in the format ‘HH:MM:SS’ can the format-string be left out.
TO_TIMESTAMP
The TO_TIMESTAMP function converts a string that is in a given format to a TIMESTAMP data type. It can also accept a number instead of a string. You can specify a literal string, a literal number, or a column that contains a string or a number. The format must correspond to a supplied format-string. Only if the string is in the format ‘DD-MON-YY HH:MM:SS’ can the format-string be left out. Notice the last example, which does not contain a format-string, so it thinks the year is 2059.
Using CASE and Extract to reformat Dates
SELECT Order_Date AS "Order_Date",
CASE
WHEN EXTRACT(month from Order_Date) < 10 THEN '0' ||
CAST(EXTRACT(month FROM Order_Date) AS CHAR(1))
ELSE
CAST(EXTRACT(month FROM Order_Date) AS CHAR(2))
END
|| '/' ||
CAST(EXTRACT(YEAR FROM Order_Date) AS CHAR(4)) AS "mmyyyy"
FROM Order_Table
ORDER BY 1, 2
Order_Date mmyyyy
05/04/1998 05/1998
01/01/1999 01/1999
09/09/1999 09/1999
10/01/1999 10/1999
10/10/1999 10/1999
Use the EXTRACT function (combined with CASE, CAST and CONCATENATION) to retrieve date and month and reformat them in the date format of mm/yyyy. The concatenation is performed by the double pipe symbols.
Using CAST and SUBSTRING to reformat Dates
SELECT Order_Date AS "Order_Date",
SUBSTRING (Cast(Order_Date as CHAR(10)) FROM 6 for 2)
|| '/' ||
SUBSTRING (CAST(Order_Date as CHAR(10)) FROM 1 for 4) AS "mmyyyy"
FROM Order_Table
ORDER BY 1, 2
Order_Date mmyyyy
05/04/1998 05/1998
01/01/1999 01/1999
09/09/1999 09/1999
10/01/1999 10/1999
10/10/1999 10/1999
Use the CAST, SUBSTRING and CONCATENATION) to retrieve date and month and reformat them in the date format of mm/yyyy. The concatenation is performed by the double pipe symbols.
Using the DAYOFWEEK and the DECODE Function
SELECT DECODE(DAYOFWEEK(Order_Date),
0, 'Sun', 1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thur', 5, 'Fri', 6, 'Sat' ) AS "DayofWeek"
,COUNT(*) as "Count"
,SUM(Order_Total) as "Sum"
,AVG(Order_Total) as "Avg"
FROM Order_Table
GROUP BY 1
ORDER BY 2 DESC
The query above uses the DAYOFWEEK function to show the day of the week. DECODE is provided as a syntax that is compatible with a number of other database vendors. It gives a subset of the capabilities of CASE expressions.
Intervals
An INTERVAL is a period of time. The INTERVAL data type has two sub-classes, consisting of either YEAR-MONTH or DAY-TIME. YEAR-MONTH intervals indicate the number of years and/or months, and consist of either a YEAR component, a MONTH component or both. The DAY-TIME Interval indicates the number of days, hours, minutes and seconds (no sub-seconds allowed) and has components for DAY, HOUR, MINUTE and SECOND.
More Interval Examples
An INTERVAL is a period of time. The INTERVAL data type has two sub-classes, consisting of either YEAR-MONTH or DAY-TIME. YEAR-MONTH intervals indicate the number of years and/or months, and consist of either a YEAR component, a MONTH component or both. The DAY-TIME Interval indicates the number of days, hours, minutes and seconds (no sub-seconds allowed) and has components for DAY, HOUR, MINUTE and SECOND.
TO_CHAR Details
TO_CHAR is a function that uses a specified format definition, (or a data
type specific default) to reformat a supplied date-time or numeric data type.
TO_CHAR(number, format-string)
TO_CHAR(date-value)
TO_CHAR(time-value)
TO_CHAR(timestamp-value)
TO_CHAR(date-value, format-string)
TO_CHAR(time-value, format-string)
TO_CHAR(timestamp-value, format-string)
9999990 |
Count of nines and zeros determines max digits to be displayed |
999,999,999.99 |
Commas and decimals are placed in the pattern shown. |
999990 |
Displays a zero only if the value is zero |
099999 |
Displays numbers with leading zeros. |
$99999 |
A Dollar sign is placed in front of every number |
B99999 |
Display will be blank if value is zero, which is the default |
99999MI |
Minus or plus sign follows the number |
99999S |
Minus or plus sign follows the number. Same as 99999MI |
S99999 |
Minus or plus sign precedes the number |
Get ready to get specific about the TO_CHAR function. There are more options on the next page.
TO_CHAR Details Continued
99D99 |
Display the decimal character in that position. |
C99999 |
Displays the ICO currency character (GBP) in that position. |
L99999 |
Displays the local currency character (£) in that position. |
£99999 |
Displays the currency character £ in that position. |
RN |
Displays as a roman numeral. |
99999PR |
Negative numbers are surrounded by < and >. |
9.999EEEE |
Display will be a scientific notation, (It must be 4 Es). |
999V99 |
Multiplies number by 10n where n is the number of digits to V’s right. |
SP |
The number is spelled out and is in upper case. |
Sp |
Same as SP but with initial capital. |
sp |
Same as SP but lowercase. |
SPTH |
The number to be spelled out in uppercase and has an ordinal suffix. |
Spth |
Same as SPTH but with an initial capital. |
spth |
Same as SPTH but lower case. |
THSP |
Same as SPTH. |
Thsp |
Same as Spth. |
thsp |
Same as spth. |
xxxxxxxx |
Display the number as a Hexadecimal number. |
Get ready to get specific about the TO_CHAR function. There are more options on the next page.
TO_CHAR, TO_DATE, TO_TIME and TO_TIMESTAMP
The following date-time formats are used with TO_CHAR,
TO_DATE, TO_TIME and TO_TIMESTAMP.
There are more options on the next page.
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
The following date-time formats are used with TO_CHAR,
TO_DATE, TO_TIME and TO_TIMESTAMP.
Format |
Action taken |
YYYY |
Four-digit year. |
SYYYY |
Signed year if BC. |
IYYY |
ISO four-digit year. |
YYY |
Last three digits of the year. |
IYY |
Last three digits of the ISO year. |
YY |
Last two digits of the year. |
IY |
Last two digits of the ISO year. |
Y |
Last digit of the year. |
I |
Last digit of the ISO year. |
YEAR |
Year spelled out. |
Year |
Same as YEAR, but with initial capitals. |
year |
Same as YEAR, but in lowercase. |
Q |
Number of the quarter. |
WW |
Number of the week in the year. |
W |
Number of week in the month. |
IW |
Week of year from the ISO standard. |
J |
“Julian” – days since Dec 31, 4713 B.C. |
There are more options on the next page.
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
The following date-time formats are used with TO_CHAR,
TO_DATE, TO_TIME and TO_TIMESTAMP.
Format |
Action taken |
HH |
Hour of day, always between 1–12. |
HH12 |
Same as HH. |
HH24 |
Hour of day, 24-hour clock. |
MI |
Minute of the hour. |
SS YY |
Second of the minute. |
SSSSS |
Seconds since midnight, always 0–86399. |
A.M. |
Display A.M. or P.M. depending on time of day. |
a.m. |
Same as A.M., but in lowercase. |
P.M. |
Same as A.M. |
p.m. |
Same as a.m. |
AM |
Same as A.M., but without any periods. |
Am |
Same as a.m., but without any periods. |
PM |
Same as P.M., but without any periods. |
pm |
Same as p.m., but without any periods. |
There are more options on the next page.
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
The following date-time formats are used with TO_CHAR,
TO_DATE, TO_TIME and TO_TIMESTAMP.
Format |
Action taken |
CC |
Century. |
SCC |
Same as CC, but prefixes BC with “-“. |
B.C. |
Displays B.C. or A.D. depending on the date. |
A.D. |
Same as B.C. |
b.c. |
Same as B.C., but in lowercase. |
a.d. |
Same as A.D., but in lowercase. |
BC. |
Same as B.C., but without any periods. |
AD |
Same as A.D., but without any periods. |
bc |
Same as b.c., but without any periods. |
ad |
Same as a.d., but without any periods. |
I hope this was enough detail to help.