Professional Web Applications Themes

Retrieve month portion of date column as 2 digits - MySQL

If the value stored in the adate column is 2006-09-24, how can the following statement be modified to return 20060924 vs. 2006924? SELECT concat(year(adate),month(adate),day(adate)) from demotable or is there a better way to retrieve a date value in the format YYYYMMDD? thanks Lee...

  1. #1

    Default Retrieve month portion of date column as 2 digits

    If the value stored in the adate column is 2006-09-24, how can the
    following statement be modified to return
    20060924 vs. 2006924?

    SELECT concat(year(adate),month(adate),day(adate)) from demotable

    or is there a better way to retrieve a date value in the format
    YYYYMMDD?

    thanks
    Lee
    Lee Guest

  2. #2

    Default Re: Retrieve month portion of date column as 2 digits

    On Thu, 21 Sep 2006 02:19:53 GMT, Lee Peedin wrote: 

    mysql> select date_format('2006-09-26', '%Y%m%d');
    +-------------------------------------+
    | date_format('2006-09-26', '%Y%m%d') |
    +-------------------------------------+
    | 20060926 |
    +-------------------------------------+
    1 row in set (0.04 sec)

    mysql> select date_format('2006-09-03', '%Y%c%d');
    +-------------------------------------+
    | date_format('2006-09-03', '%Y%c%d') |
    +-------------------------------------+
    | 2006903 |
    +-------------------------------------+
    1 row in set (0.00 sec)

    mysql> select date_format('2006-09-03', '%Y%c%e');
    +-------------------------------------+
    | date_format('2006-09-03', '%Y%c%e') |
    +-------------------------------------+
    | 200693 |
    +-------------------------------------+
    1 row in set (0.00 sec)

    mysql>

    See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
    for even more fun and games.

    --
    Every normal man must be tempted at times to spit upon his hands, hoist
    the black flag, and begin slitting throats.
    -- HL Mencken
    Peter Guest

  3. #3

    Default Re: Retrieve month portion of date column as 2 digits

    >If the value stored in the adate column is 2006-09-24, how can the 

    I suggest:
    SELECT date_format(adate, '%Y%m%d') from demotable;

    which allows a lot more flexibility with formats.

    Gordon L. Burditt
    Gordon Guest

  4. #4

    Default Re: Retrieve month portion of date column as 2 digits

    Peter & Gordon :-)
    Thanks to both of you - as you can probably tell, I'm a newbie. Your
    responses were exactly what I needed.

    Lee

    BTW: Unless you're as old as I am, you probably won't understand the
    smiley following "Peter & Gordon".
    <http://www.classicbands.com/pandg.html>

    On Thu, 21 Sep 2006 02:38:56 -0000, org (Gordon
    Burditt) wrote:
     
    >
    >I suggest:
    >SELECT date_format(adate, '%Y%m%d') from demotable;
    >
    >which allows a lot more flexibility with formats.
    >
    > Gordon L. Burditt[/ref]

    Lee Guest

Similar Threads

  1. Start and end date of month
    By ccnorris in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: July 19th, 08:46 PM
  2. SQL: can a date ONLY contain month/year info?
    By darrel in forum Dreamweaver AppDev
    Replies: 4
    Last Post: March 28th, 02:53 PM
  3. 2 digit Date and month format
    By tony in forum ASP
    Replies: 3
    Last Post: January 30th, 10:48 AM
  4. Last Date of previous month
    By navdeep virk in forum Informix
    Replies: 9
    Last Post: November 11th, 02:47 PM
  5. Update portion of a string column
    By Yves Glodt in forum IBM DB2
    Replies: 2
    Last Post: September 12th, 06:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139