How to query only rows containing date data between two dates.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Luk3r
    Contributor
    • Jan 2014
    • 300

    How to query only rows containing date data between two dates.

    Okay, so I've been all over Google without much luck. I'm trying to query a column that contains dates in this format (1/1/2014, 1/2/2014, etc.). The column is nchar (which I'm aware should be changed), but I didn't figure this would be an issue. What's happening is when I search for a date starting at 1/1/2014, the query also returns results with 1/10/2014, 1/11/2014, and so on. Any help would be much appreciated! Here's the simple query I was using:

    Code:
    select *
    from WrapUpDataTable1
    where WrapUpDataTable1.Date >= '1/1/2014'
      and WrapUpDataTable1.Date <= '1/3/2014'
    Last edited by NeoPa; Jan 14 '14, 04:38 PM. Reason: Please use the (Mandatory) [CODE] tags when posting code.
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32636

    #2
    Unless you have a very good reason for not doing so, I would suggest you follow the more straightforward approach of storing the data you want. IE. Dates rather than strings that look like dates.

    If you must use a string value then you should, at least, format it in such a way that it is compatible regardless of the values (EG. mm/dd/yyyy rather than m/d/yyyy).

    If you must have a string and would rather have an easier life, don't use a standard date format at all, but something that orders the values left-to-right as necessary for sorting. EG. yyyy/mm/dd.

    I would imagine that extracting the values you want from your strings using T-SQL would be quite painful and messy.
    Last edited by NeoPa; Jan 14 '14, 04:46 PM.

    Comment

    • Luk3r
      Contributor
      • Jan 2014
      • 300

      #3
      Thanks, NeoPa. I was hoping that wasn't the only way to go about this, but it makes the most sense. I had/have every intention of changing from string to date format. This was more of a "why doesn't it work?" and less of a "how do I make it work?"...since I understand that the table is already set up incorrectly :) Thanks a lot for the response!

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32636

        #4
        Are you still interested in more explanations of how/why the alternative suggestions would work?

        It's perfectly reasonable to ask technical questions that you aren't, necessarily, considering putting into practice. If you want further understanding then I'm happy to oblige.

        I would always explain the caveat that it isn't a good approach though. Even if you appreciate that already, many that find and read this later on may not.

        Comment

        • Luk3r
          Contributor
          • Jan 2014
          • 300

          #5
          NeoPa, I was more interested in knowing if there was an answer to my question while using string instead of date. I wasn't expecting much, except to be told that I should be using Date, and possibly a 'workaround' until I can recreate this table. But if there is no way to work around it, then I will just take my time and change that column to date format when I get a chance. I appreciate your offer to help, though! :)

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32636

            #6
            There is a way to handle that in T-SQL. It would be very fiddly though, due to the unpredictabilit y of where the relevant data is within the string. The month parts start in different positions for 1/1/2013 and 12/31/2013, for instance.

            A fairly long and involved formula could be set up which would use the following functions :
            RIGHT(), CHARINDEX() & SUBSTRING().

            Comment

            • Rabbit
              Recognized Expert MVP
              • Jan 2007
              • 12517

              #7
              A workaround would be to use the CDate function to convert the strings into a date. Assuming of course that the string format matches the system format.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32636

                #8
                I'm not sure T-SQL has a CDate() function. You may find using CONVERT() is an easier approach than what i was thinking of mind. Good idea Rabbit. My T-SQL is pretty rusty now TBF.

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517

                  #9
                  Oh right, this is the SQL Server forum. For some reason I thought it was VBA, yes, CONVERT or CAST could be used as a workaround if you are limited to strings.

                  Comment

                  • Luk3r
                    Contributor
                    • Jan 2014
                    • 300

                    #10
                    Thanks, Rabbit and NeoPa. I'll look into CONVERT and CAST simply for learning purposes. But I'll go ahead and recreate the table with that column only accepting DATES. I appreciate the help.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32636

                      #11
                      I think what Rabbit means to say is that seeing me post in SQL-Server is very rare nowadays and that led him to assume it must be an Access question :-D

                      I can sympathise. It's been a while since I ventured into this forum.

                      @Luk3r
                      That seems a very sensible approach. It's been a pleasure working with you.

                      Comment

                      Working...