Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17

    Question Unanswered: Converting Date Formats in Query

    I have a table right now with dates such as 20040314 that would be YYYYMMDD.

    My question is: How do you convert a date in a table that has date such as 20040314(yyyymmdd) to 031404(mmddyy)?? I'd like to do this in a query if possible, if that makes it easier since I'll be exporting data from there.

    Is there a date function to do this?? I was trying to do a Substr function combined with a concatenate function to accomplish this, but had no luck.


    Thanks for all your help and consideration,


    Tony

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the FORMAT() function
    PHP Code:
    select format(datefield,"mmddyy") ... 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17
    Ok that's cool..

    but I have to in some way tell it what the format is right now...right?

    for instance I have some other dates like:

    1040313 where "1" equals the century, "04" is the year, "03" is the month and "13" is the day.

    I would like to re-arrange the date to be 031304 (mmddyy), is this possible with the Format function? I think I'd have to incorporate the IIF function as well, right?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the FORMAT function works on DATE/TIME datatypes and if your dates aren't DATE/TIME datatypes....

    if you've got a value like 1040313 then presumabley it's not a DATE/TIME column, right?

    yes, you'd have to convert that value yourself

    what datatype is it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17
    Yes I'll have to convert it I guess....

    Right now the datatype is text.

    The data is being imported into Access this way form another source.

    So I'll have to convert it, then put it into MMDDYY format.


    Yes, I need to convert from a text field to a date.


    OR

    if this can't be done, how can I take simple text such as "01040313" and make it "031304" by re-arranging it???



    Any ideas???
    Last edited by Tony DBA; 03-30-04 at 21:05.

  6. #6
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17
    This is what I needed:

    Expr1: Mid([table name].[datefield],4,2) & Mid([table name].[datefield],6,2) & Mid([table name].[datefield],2,2)


    I figured it out....

Posting Permissions

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