SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)

    Access SQL - Formating Dates

    I've got a table that I need to store date and time in form of "3/1/2011 10:41:36 AM". I know that I can select this through SQL with "format([date], 'mm/dd/yy'". However how can I run criteria against these formated dates? Where date()-1 and so fourth. I cannot get any results.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do not store formatted dates, use the DATETIME datatype instead

    that's the only way you will get good results from date calculations etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Unfortunately I'm limited by the data I receive. I have no say in how its stored. I did manage to figure out how to do what Im looking for the most part.

    Is there a way to do a complete outer join in access? Im trying to do a count on some fields that are completed within the last week, some may have some counts, some may not. Im either looking at a full outter join between 3 tables (which I have no idea how to do) or looking at creating an expression that uses access' date function to populate the dates of those 7 days in the week.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there's a lot going on here that i don't understand

    first, you say you "need to" store formatted dates, suggesting that the output formatting is what you want, now you say you have no say in how it is stored

    yes, there is a way to do a full outer join in access, but that's not what you want, it sounds like you need only a left outer join, assuming you can join a table of desired dates with your data based on this formatted date

    Edit: see http://www.sitepoint.com/forums/showthread.php?t=736408

    "within the last week" is harder to do if you are dealing with VARCHARs that contain strings that look like dates, because you cannot do date arithmetic with strings

    so you are unfortunately down to creating a match based on a list of seven formatted date strings which you would then compare to your data based on the leftmost 8-10 characters (to allow for the difference in string length between 3/1/2011 and 03/01/2011) so as to ignore the time portion of the string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Seems to be alot of confusion here. Firstly The dates are stored in DATETIME format. Which I just realized I don't need to format at all to run datdiff() against.

    But Im still left with the join issue. I have 3 tables. All three may have dates that do not have corresponding dates on the other. But None the less I need to merge all dates into one column on the left.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are right, there is a lot of confusion

    let's eliminate it by having you show us the actual table layouts (column names and datatypes) for all tables involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •