Difference between revisions of "Plsql date"
From John Freier
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | Parameter Explanation | + | To use the current date use '''sysdate''' |
| − | YEAR Year, spelled out | + | select sysdate from dual; |
| − | YYYY 4-digit year | + | |
| − | YYY | + | To specify a date. |
| − | YY | + | Parameter Explanation |
| − | Y Last 3, 2, or 1 digit(s) of year. | + | YEAR Year, spelled out |
| − | IYY | + | YYYY 4-digit year |
| − | IY | + | YYY |
| − | I Last 3, 2, or 1 digit(s) of ISO year. | + | YY |
| − | IYYY 4-digit year based on the ISO standard | + | Y Last 3, 2, or 1 digit(s) of year. |
| − | RRRR Accepts a 2-digit year and returns a 4-digit year. | + | IYY |
| − | A value between 0-49 will return a 20xx year. | + | IY |
| − | A value between 50-99 will return a 19xx year. | + | I Last 3, 2, or 1 digit(s) of ISO year. |
| − | Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1). | + | IYYY 4-digit year based on the ISO standard |
| − | MM Month (01-12; JAN = 01). | + | RRRR Accepts a 2-digit year and returns a 4-digit year. |
| − | MON Abbreviated name of month. | + | A value between 0-49 will return a 20xx year. |
| − | MONTH Name of month, padded with blanks to length of 9 characters. | + | A value between 50-99 will return a 19xx year. |
| − | RM Roman numeral month (I-XII; JAN = I). | + | Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| − | WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. | + | MM Month (01-12; JAN = 01). |
| − | W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. | + | MON Abbreviated name of month. |
| − | IW Week of year (1-52 or 1-53) based on the ISO standard. | + | MONTH Name of month, padded with blanks to length of 9 characters. |
| − | D Day of week (1-7). | + | RM Roman numeral month (I-XII; JAN = I). |
| − | DAY Name of day. | + | WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| − | DD Day of month (1-31). | + | W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| − | DDD Day of year (1-366). | + | IW Week of year (1-52 or 1-53) based on the ISO standard. |
| − | DY Abbreviated name of day. | + | D Day of week (1-7). |
| − | J Julian day; the number of days since January 1, 4712 BC. | + | DAY Name of day. |
| − | HH Hour of day (1-12). | + | DD Day of month (1-31). |
| − | HH12 Hour of day (1-12). | + | DDD Day of year (1-366). |
| − | HH24 Hour of day (0-23). | + | DY Abbreviated name of day. |
| − | MI Minute (0-59). | + | J Julian day; the number of days since January 1, 4712 BC. |
| − | SS Second (0-59). | + | HH Hour of day (1-12). |
| − | SSSSS Seconds past midnight (0-86399). | + | HH12 Hour of day (1-12). |
| − | FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. | + | HH24 Hour of day (0-23). |
| − | + | MI Minute (0-59). | |
| − | AD or A.D AD indicator | + | SS Second (0-59). |
| − | BC or B.C. BC indicator | + | SSSSS Seconds past midnight (0-86399). |
| − | TZD Daylight savings information. For example, 'PST' | + | FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. |
| − | TZH Time zone hour. | + | AD or A.D AD indicator |
| − | TZM Time zone minute. | + | BC or B.C. BC indicator |
| − | TZR Time zone region. | + | TZD Daylight savings information. For example, 'PST' |
| + | TZH Time zone hour. | ||
| + | TZM Time zone minute. | ||
| + | TZR Time zone region. | ||
to_date('2003/07/09', 'yyyy/mm/dd') | to_date('2003/07/09', 'yyyy/mm/dd') | ||
to_date('070903', 'MMDDYY') | to_date('070903', 'MMDDYY') | ||
to_date('20020315', 'yyyymmdd') | to_date('20020315', 'yyyymmdd') | ||
Latest revision as of 11:33, 15 July 2013
To use the current date use sysdate
select sysdate from dual;
To specify a date.
Parameter Explanation YEAR Year, spelled out YYYY 4-digit year YYY YY Y Last 3, 2, or 1 digit(s) of year. IYY IY I Last 3, 2, or 1 digit(s) of ISO year. IYYY 4-digit year based on the ISO standard RRRR Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year. Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1). MM Month (01-12; JAN = 01). MON Abbreviated name of month. MONTH Name of month, padded with blanks to length of 9 characters. RM Roman numeral month (I-XII; JAN = I). WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. IW Week of year (1-52 or 1-53) based on the ISO standard. D Day of week (1-7). DAY Name of day. DD Day of month (1-31). DDD Day of year (1-366). DY Abbreviated name of day. J Julian day; the number of days since January 1, 4712 BC. HH Hour of day (1-12). HH12 Hour of day (1-12). HH24 Hour of day (0-23). MI Minute (0-59). SS Second (0-59). SSSSS Seconds past midnight (0-86399). FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. AD or A.D AD indicator BC or B.C. BC indicator TZD Daylight savings information. For example, 'PST' TZH Time zone hour. TZM Time zone minute. TZR Time zone region.
to_date('2003/07/09', 'yyyy/mm/dd')
to_date('070903', 'MMDDYY')
to_date('20020315', 'yyyymmdd')
