Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2012
    Posts
    19

    Unanswered: How do I write a WHERE clause to pick Jun and 2012 out of the string Jun320129AM?

    I have a table called "appointments". This table contains a column called "date_time". This column contains the dates and times of appointments. The data type is varchar.

    An example of the string is:
    Code:
    Jun320129AM - that is Jun 3 2012 9AM all jammed together as a string
    I need a way to do a SELECT search for these appointments for any given month. For example, a search for all appointments in the month of June 2012 would have to be able to pick out these two parts:
    Code:
    Jun and 2012
    from the string:
    Code:
    Jun320129AM
    I assume such a SELECT search needs a very precisely written WHERE clause. How do I write such a clause?

    Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the possible year range(from year and to year) in the column?

  3. #3
    Join Date
    Mar 2012
    Posts
    19
    The table stores dates that are associated with a calendar written in php. The strings are created as part of the for loop that creates the calendar.

    Considering the fact that the calendar moves forward in an unlimited way ( a calendar that stops 10 years in the future would only be good for ten years - not good), it is unlimited in the future.

    It is limited in the other direction only by its start time. For example, if I put it into action February 2015, then I don't have to search for dates earlier than that.
    Last edited by quartz; 07-01-12 at 21:22.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...it is unlimited in the future.
    If so, ambiguous cases may happen.

    For example:
    Aug1220211AM

    Aug 1 2202 11AM
    Aug 12 2021 1AM

  5. #5
    Join Date
    Mar 2012
    Posts
    19
    Very true. I would have to find a way to create separate components of the string.

    For example the 3 letter month would be 1 component, followed by the day, etc.

    Separate string and table column for each "component".

    Or find another way entirely to store and search dates.

    Thanks for your help - appreciate it.
    Last edited by quartz; 07-01-12 at 22:15.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    why would you not just use datetime for your column type and store your data correctly?

  7. #7
    Join Date
    Mar 2012
    Posts
    19
    Quote Originally Posted by guelphdad View Post
    why would you not just use datetime for your column type and store your data correctly?
    I found a solution to the problem. Thanks for taking the time to read my post.

    I didn't ( and still don't ) want to use the datetime data type because the entire script is based on a very complex php calendar that does some very complex things very well.

    Part of how it works is it creates those strings as part of the way it creates html element id's, therefore determining how ajax displays precise calendar information.

    This complex, but very important script is not compatible with the datetime data format. It would be inefficient to change a very complex and helpful script simply to make it compatible with a smaller, simpler script that does much less.

    Its more efficient to change the simpler script to make it compatible with the more complex.

    I found a way to do it. The string I displayed earlier in this thread is created by concatenating several php variables together. I will simply add a new section to the script, and within that new section separate the variables so each one is its own string, and insert each into its own column in a new table.

    That will separate out all the pieces of that string, in one specific place within the for loop, so I can write queries to search that table. The rest of this very complex script does not have to do that type of search. It can stay the way it is.

    The script is too long and complex to post here or explain, but this is the solution I was looking for, and the answers I received in this thread helped a lot.

    Thanks very much for the help.
    Last edited by quartz; 07-01-12 at 23:38.

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    It is quite possible that storing the value as datatime will be to your advantage. . .

    When the "php format" is needed re-arrange the value to this format in the select.

    For every other use, the datetime would work quite well -without having the added complexity in all of the code to parse out the date. For ordering things or comparing by date, the datetime should be much easier to use than the non-standard format.

    Possibly i misunderstand something.

  9. #9
    Join Date
    Mar 2012
    Posts
    19
    papadi, thanks for your reply. You said a lot in a small paragraph. I think you are saying find a way to use the datetime datatype because it makes everything easier.

    The concern I have is everything in this calendar is created by php variables in the for loop. I would have to create several variables and manipulate them so they "look like" the datetime format.

    If I did that would mysql accept that?

    I'll think about this more, and come back to this thread, after a few hours sleep, to see if there are any more replies.

    Thanks again to everyone.

  10. #10
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486

    pick date values out of a string

    I think you are saying find a way to use the datetime datatype because it makes everything easier.
    My intent was not so much that it will be "easier to code" but that i believe it will be easier to understand when someone needs to maintain this in the future when it is no longer "fresh" in people's minds.

    If this column is ever to be used in reporting or driving some process (i.e.. ordering for some select comparing against some other date in a different column or row), using datetime (imho) will be much more efficient and easier to maintain.

  11. #11
    Join Date
    Mar 2012
    Posts
    19
    Quote Originally Posted by papadi View Post

    When the "php format" is needed re-arrange the value to this format in the select.
    Could you explain this in a little more detail?

  12. #12
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    You can reformat the standard datetime value to the custom "php format" when that format actually needs to be used. I suspect that most processing should work with the standard datetime.

    Possibly there is something i misunderstand?

  13. #13
    Join Date
    Mar 2012
    Posts
    19
    Quote Originally Posted by papadi View Post
    You can reformat the standard datetime value to the custom "php format" when that format actually needs to be used. I suspect that most processing should work with the standard datetime.

    Possibly there is something i misunderstand?
    My script is too big and complex to post here, so unfortunately you won't be able to see it. I appreciate your advice. I've made a reminder note to myself.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    What are the possible year range(from year and to year) in the column?
    At that time in my mind was if year range was limited to 2000-2099(or may be 2000-2199),
    ambiguity might be removed by finding the position of '20' was 5 or 6.

    For example:
    Aug1220211AM is Aug 12 2021 1AM and not Aug 1 2202 11AM
    Aug2202111AM is Aug 2 2021 11AM and not Aug 22 0211 1AM
    Aug3120211AM is Aug 31 2021 1AM and not Aug 3 1202 11AM
    Aug1221211AM is Aug 12 2121 1AM and not Aug 1 2212 11AM
    Aug2212111AM is Aug 2 2121 11AM and not Aug 22 1211 1AM
    Last edited by tonkuma; 07-03-12 at 17:39. Reason: Add another data "Aug3120211AM".

  15. #15
    Join Date
    Mar 2012
    Posts
    19
    Quote Originally Posted by tonkuma View Post
    If so, ambiguous cases may happen.

    For example:
    Aug1220211AM

    Aug 1 2202 11AM
    Aug 12 2021 1AM
    hi tonkuma,

    Yes, you're right. Ambiguous cases may happen. This comment from you showed me that even if I did find a way, the ambiguity would cause problems.

    I did find a solution.

    That long concatenated string is a placeholder for the insertion of future php variables inside a for loop. The placeholder was there while I was writing and testing the javascript.

    My problem was the long version worked really well for several important functions. I didn't want to break it up just for one or two small scripts to work.

    I realized I can put the long string in the places it will work best, and I can put the individual variables in a different place in the same script where they will work best.

    It doesn't have to be all one way or all the other. Php doesn't really see the long string as a unit of information. It sees the individual variables within it.

    This way the separated variables can be inserted into separate columns in a table created for a particular search I will have to be able to do.

    It works. Thank for your help.
    Last edited by quartz; 07-03-12 at 12:06.

Posting Permissions

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