On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Thursday, August 11, 2011

Wanna' Date?


Just a picture of 99% of the date variations listed in the book, although I'm saving timestamps for another post.

The Easy Way
 Select To_Char(Sysdate,'DL'   ) DL   /* long date */
      , To_Char(Sysdate,'DS'   ) DS   /* short date */
      , To_Char(Sysdate,'TS'   ) TS   /* short time */
      , To_Char(Sysdate,'DS TS') DSTS /* short date, time */
  From Dual;
DL                        | DS       | TS         | DSTS                
------------------------- | -------- | ---------- | --------------------
Saturday, August 06, 2011 | 8/6/2011 | 9:27:37 AM | 8/6/2011 09:27:37 AM

Day
Select To_Char(Sysdate,'DAY'   ) "DAY"    /* Name (Uppercase) */
     , To_Char(Sysdate,'Day'   ) "Day"    /* Name (Title case) */
     , To_Char(Sysdate,'DY'    ) "DY"     /* Abbreviated (Uppercase). */
     , To_Char(Sysdate,'Dy'    ) "Dy"     /* Abbreviated (Title case). */
     , To_Char(Sysdate,'D'     ) "D"      /* Day of week (1-7). */
     , To_Char(Sysdate,'DD'    ) "DD"     /* Day of month (1-31). */
     , To_Char(Sysdate,'fmDD'  ) "fmDD"   /* Day of month (1-31) zero suppress */
     , To_Char(Sysdate,'DDD'   ) "DDD"    /* Day of year (1-366). */
     , To_Char(Sysdate,'DDsp'  ) "DDsp"   /* spell out (Uppercase)*/
     , To_Char(Sysdate,'Ddsp'  ) "Ddsp"   /* spell out (Titlecase)*/
     , To_Char(Sysdate,'DDth'  ) "DDth"   /* ordinal (Uppercase) */
     , To_Char(Sysdate,'Ddth'  ) "Ddth"   /* ordinal (Title case) */
     , To_Char(Sysdate,'fmDdth') "fmDdth" /* ordinal zero suppress */
     , To_Char(Sysdate,'J'     ) "J"      /* Julian:# days since 1/1/4712BC */
  From Dual;

DAY      | Day      | DY  | Dy  | D | DD | fmDD | DDD | DDsp | Ddsp | DDth | Ddth | fmDdth | J
-------- | -------- | --- | --- | - | -- | ---- | --- | ---- | ---- | ---- | ---- | ------ | -------
SATURDAY | Saturday | SAT | Sat | 7 | 06 | 6    | 218 | SIX  | Six  | 06TH | 06th | 6th    | 2455780

Embed in Format - / , ; : . "text"
Select To_Char(Sysdate,'"Day": Dy, "Month": Month')
  From Dual;

TO_CHAR(SYSDATE,'"DAY":DY,
--------------------------
Day: Sat, Month: August


Century

Select To_Char(Sysdate,'AD')  "AD"   /* AD or BC */
     , To_Char(Sysdate,'A.D.')"A.D." /* A.D or B.C. */
     , To_Char(Sysdate,'B.C.')"B.C." /* A.D or B.C. */
  From Dual;

AD | A.D. | B.C.
-- | ---- | ----
AD | A.D. | A.D.


Month /* use fm in front to remove padding */

Select To_Char(Sysdate,'MM')      "MM"     /* 01-12 */
     , To_Char(Sysdate,'Month')   "Month"  /* Name padded with blanks to widest name */
     , To_Char(Sysdate,'fmMonth') "fmMonth"/* Name not padded */
     , To_Char(Sysdate,'MONTH')   "MONTH"  /* NAME padded with blanks to widest name */
     , To_Char(Sysdate,'Mon')     "Mon"    /* Abbreviated name (title case) */
     , To_Char(Sysdate,'MON')     "MON"    /* Abbreviated name (uppercase) */
     , To_Char(Sysdate,'RM')      "RM"     /* Roman numeral month */
  From Dual;

MM | Month     | fmMonth | MONTH  | Mon | MON | RM
-- | --------- | ------- | ------ | --- | --- | ----
08 | August    | August  | AUGUST | Aug | AUG | VIII


Week

Select To_Char(Sysdate,'WW')     "WW"    /* Week of year (1-53) where week 1 = 1/1 to 1/7. */
     , To_Char(Sysdate,'WWth')   "WWth"  /* Week of year (1ST-53RD) */
     , To_Char(Sysdate,'Wwth')   "Wwth"  /* Week of year (1st-53rd) */
     , To_Char(Sysdate,'WWsp')   "WWsp"  /* Week of year spelled out in caps */
     , To_Char(Sysdate,'Wwsp')   "Wwsp"  /* Week of year spelled out in title case*/
     , To_Char(Sysdate,'WWthsp') "WWthsp"/* Week of year (1st-53rd) spelled out*/
     , To_Char(Sysdate,'W')      "W"     /* Week of month (1-5) where week 1 = 1st to 7th*/
     , To_Char(Sysdate,'Wth')    "Wth"   /* Week of month (1st-5th) */
  From Dual;

WW | WWth | Wwth | WWsp       | Wwsp       | WWthsp        | W | Wth
-- | ---- | ---- | ---------- | ---------- | ------------- | - | ---
32 | 32ND | 32nd | THIRTY-TWO | Thirty-Two | THIRTY-SECOND | 1 | 1st


Quarter

Select To_Char(Sysdate,'Q')     "Q"     /* Quarter of year (1=Jan-Mar, 2=Apr-Jun, etc)*/
     , To_Char(Sysdate,'Qth')   "Qth"   /* Quarter of year (1st - 4th */
     , To_Char(Sysdate,'Qsp')   "Qsp"   /* Quarter of year (One - Four) */
     , To_Char(Sysdate,'Qthsp') "Qthsp" /* Quarter of year (First - Fourth) */
  From Dual;

Q | Qth | Qsp   | Qthsp
- | --- | ----- | -----
3 | 3rd | Three | Third


Year

Select To_Char(Sysdate,'Year')  "Year"  /* Spell out year */
     , To_Char(Sysdate,'RR')    "RR"    /* store 20th century dates in 21st using 2 digits */
     , To_Char(Sysdate,'RRRR')  "RRRR"  /* Round for 4 or 2. If 2, provides the same return as RR. */
     , To_Char(Sysdate,'Y,YYY') "Y,YYY" /* Year with comma */
     , To_Char(Sysdate,'YEAR')  "YEAR"  /* spelled out */
     , To_Char(Sysdate,'YYYY')  "YYYY"  /* 4-digit year */
     , To_Char(Sysdate,'YYY')   "YYY"   /* Last 3 digits */
     , To_Char(Sysdate,'YY')    "YY"    /* Last 2 digits */
     , To_Char(Sysdate,'Y')     "Y"     /* Last digit */
  From Dual;

Year          | RR | RRRR | Y,YYY | YEAR          | YYYY | YYY | YY | Y
------------- | -- | ---- | ----- | ------------- | ---- | --- | -- | -
Twenty Eleven | 11 | 2011 | 2,011 | TWENTY ELEVEN | 2011 | 011 | 11 | 1


Time

Select To_Char(Sysdate,'AM')    "AM"   /* Meridian indicator */
     , To_Char(Sysdate,'a.m.')  "a.m." /* Meridian indicator */
     , To_Char(Sysdate,'HH')    "HH"   /* Hour of day (1-12). */
     , To_Char(Sysdate,'fmHH')  "fmHH" /* Hour of day (1-12). */
     , To_Char(Sysdate,'HH24')  "HH24" /* Hour of day (0-23). */
     , To_Char(Sysdate,'MI')    "MI"   /* Minute (0-59). */
     , To_Char(Sysdate,'SS')    "SS"   /* Second (0-59). */
     , To_Char(Sysdate,'SSSSS') "SSSSS"/* Seconds past midnight (0-86399)*/
  From Dual;

AM | a.m. | HH | fm | HH | MI | SS | SSSSS
-- | ---- | -- | -- | -- | -- | -- | -----
AM | a.m. | 09 | 9  | 09 | 27 | 37 | 34057

No comments:

Post a Comment