SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql date field

    Using asp and mysql, How do I get year, month and date out of a date type field YYYY-M-D?

    in phpmyadmin value looks like 2002-9-3
    if I display results in asp page it looks like this 9/3/02
    How do I get three fields out of this?

    <%
    ...
    date = results.fields("date")
    y = right(date,4)
    m = stuff before first /
    d = stuff between //

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    asp not required, pull it out of mysql exactly how you want it

    select DATE_FORMAT(yourdate,'%Y-%c-%e')

    note Y uppercase, c and e lowercase (although in the case of c and e there are no uppercase formats to confuse them with)

    see 6.3.4 Date and Time Functions

    rudy
    http://rudy.ca/

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, so I have to add another query? Right now I just select all, play with results, and stick them in the form values. Can I add it to my query?

  4. #4
    SitePoint Enthusiast probonic's Avatar
    Join Date
    Apr 2002
    Location
    Manchester, UK Insanity: 97%
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You do not have to add another query. You can simply put in "DATE_FORMAT(blah)" where you would normally have just put "blah" on its own. For example if you wanted the date in its original format *and* in the new format, you could just do

    select yourdate, DATE_FORMAT(yourdate,'%Y-%c-%e') FROM table WHERE .......

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But instead of now where I do
    select * from table where number = querystring
    execute
    define variables
    id = results.fields("id")
    company - results.fields("company")
    date = results.fields("date")

    I do
    select id, company, date, DATE_FORMAT(date,'%Y-%c-%e')etc.?
    execute
    define variables
    id = results.fields("id")
    company - results.fields("company")
    date = results.fields("date")
    Y = ?
    m = ?
    d = ?

    I want to have a form field named, y, m, and d not just display the formated date in one field...
    Last edited by jamesb; Sep 13, 2002 at 08:20.

  6. #6
    SitePoint Enthusiast probonic's Avatar
    Join Date
    Apr 2002
    Location
    Manchester, UK Insanity: 97%
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want the fields seperately you should be able to do:

    select *, DATE_FORMAT(date,'%Y'), DATE_FORMAT(date,'%c'), DATE_FORMAT(date,'%e')....

    which would of course include all the data you used to get, plus the year, month and day tacked onto the end as 3 extra fields.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    separate fields?

    select id, company, yourdate
    , DATE_FORMAT(yourdate,'%Y') as YYYY
    , DATE_FORMAT(yourdate,'%c') as M
    , DATE_FORMAT(yourdate,'%e') as D

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey I appreciate the help, but I'm getting confused. See on the edit page, I want to have a drop down for Month, one for Day, and a text field for year - which gets populated from the db. Thus I want to populate 3 separate form fields from the one db date type field value. (I'm trying not to use 3 different database fields which I know how to do).

    DO I DO probonic's suggestion:

    select *, DATE_FORMAT(date,'%Y'), DATE_FORMAT(date,'%c'), DATE_FORMAT(date,'%e')....

    and when I get to defining, how do I define y, m, and d?

    date = "results.fields("date")
    y = results.fields????
    m = results.fields????
    d = results.fields????

    OR DO I DO r937's suggestion:

    select id, company, yourdate
    , DATE_FORMAT(yourdate,'%Y') as YYYY
    , DATE_FORMAT(yourdate,'%c') as M
    , DATE_FORMAT(yourdate,'%e') as D

    and add all the other 27 fields to the select and when I get to defining the results:

    date = "results.fields("date")
    y = results.fields("YYYY")
    m = results.fields("M")
    d = results.fields("D")

    do that?

    Does this make any sense? I know I can probably just parse the year, month and day out of my date variable using asp, but wasn't sure how or if that was the best way.

    I'm sorry if I'm not understanding this correctly.

  9. #9
    SitePoint Enthusiast probonic's Avatar
    Join Date
    Apr 2002
    Location
    Manchester, UK Insanity: 97%
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was just about to edit my post to say how to get the values in a better way when r937 posted his response.

    r937's way is better because you can, as you said, reference them as "D", "M", and "YYYY". Like I said without the "as" bit, you would need to reference them as indexes.

    So yes, r937's is the better way.

    But you say you have 27 other fields, so the best way would be to do

    select *
    , DATE_FORMAT(yourdate,'%Y') as YYYY
    , DATE_FORMAT(yourdate,'%c') as M
    , DATE_FORMAT(yourdate,'%e') as D

    as this would include ALL fields, including the three fields for the date. So if in a record the date was "9-7-2002", you would get "9" in M, "7" in D and "2002" in YYYY, as well as all your other 27 fields.
    Last edited by probonic; Sep 13, 2002 at 08:51.

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks, I got it to work, whew... I actually used the "select *, DATE_FORMAT(..." from you and the "as YYYY" from r937 so I wouldn't have to write out 30 form fields in the select statement. So I'm happy and learned something cool! Thanks again!

    (I wrote this before I read all of your message)

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you guys can call me rudy instead of r937, although i like them both

    thought i'd mention this article -- WHY SELECT * IS BAD

    rudy
    http://rudy.ca/


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
  •