dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] SQL - Between two dates issue

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    364

    Resolved [RESOLVED] SQL - Between two dates issue

    Hi
    I am trying to get this thing to work but it seems that either the date format is not correct or I am doing some other things wrong or both.

    Code:
    Select  Top 10  Supplier, SUM([Count Of updates]) As [Count Of updates]
                                    From(
                                    SELECT  QDOC.DocNr_Clean As [Doc Nr.],  COUNT(*) As [Count Of updates], OPD.Supplier
                                     FROM DMS_OPD_QDOC As QDOC
                                     Left join DMS_OPD_ReadingList As OPD
                                     On QDOC.DocNr_Clean = OPD.DocNr
                                     Where QDOC.DocStatus <> 'Draft' And QDOC.DocEffectiveDate is Not null And QDOC.DocVer like '%.0'
                                    And DocNr_Clean in (Select Distinct DocNr From DMS_OPD_ReadingList )
                                    And  (DocApprovedDate >= '2017/01/01' and DocApprovedDate < '2030/01/01')
                                    GROUP BY QDOC.DocNr_Clean,OPD.Supplier
                                    )Temp
                                    
                                    Group by Supplier
                                    Order by [Count Of updates] DESC
    Each of the dates on their own gives the correct result, however, when combined:
    Code:
    (DocApprovedDate >= '2017/01/01' and DocApprovedDate < '2019/01/01')
    , there is no result. I have tried to cast/convert them as datetime but no correct result was produced. I could not see any fault in the logic but I ahve been worn before. One note, these dated here are outside the range of the actual data; but that shouldn't be a problem right?
    Would anyone please give me help here?
    Oh, btw, I have searched the net, not the entire net but in some reasonable scope and could not find any solution. I have some screenshots as proof if needed.
    Thanks.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,239

    Re: SQL - Between two dates issue

    What database system are you using? (eg: SQL Server 2010 Express, Access Accdb, Oracle, MySQL ....) Note that you should always mention this when asking SQL questions, as it often makes a difference.

    What data type is the field? (if it isn't DateTime or similar, show us some example values from it)

    Are there date values inside your specified range? (eg: for >= '2017/01/01' and < '2019/01/01', are there values in the table for 2017 and/or 2018?)

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    364

    Re: SQL - Between two dates issue

    Sorry about that, will keep that in mind in the future.
    The server says: Windows Server 2016 Standard (10.0)
    I noticed that the column DocApprovedDate is of type nvarchar example (21-08-2019 00:00:00). I don't know who put a date in a string column (I think I did it) but that could be one of the problems? The values in the column are from 01-Jan-2019 to 06-10-2019. I have widen to the range the the example above to see if I could get all the result. But users can actually change those dates and have values outside the actual range. Does it matter? won't the query give me everything till its greatest date if I e.g. ask for < 2050/01/01 and there is date till 2019/01/01?

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,580

    Re: SQL - Between two dates issue

    I do not know your sql server codepage but i never do this "2017/01/01"

    It's either '2019-01-01' or '20190101'
    You are not trying either of the 2 options I wrote. Try one of those, see if it works. You might be forced to "cast" to datetime the nvarchar field if it does not work. If you cast to datetime (or convert to datetime) then maybe it will also work with slash /
    Last edited by sapator; Nov 6th, 2019 at 10:54 AM.
    Slow as hell.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,239

    Re: SQL - Between two dates issue

    Quote Originally Posted by Grand View Post
    The server says: Windows Server 2016 Standard (10.0)
    That isn't the database system, that is the version of Windows for the computer it is on.

    Based on the rest of your post, I would guess that your database is one of the versions of SQL Server.

    I noticed that the column DocApprovedDate is of type nvarchar example (21-08-2019 00:00:00). I don't know who put a date in a string column (I think I did it) but that could be one of the problems?
    It would definitely be a problem.

    Values should be stored using a data type that is designed for them (so for dates and times, use DateTime or similar).

    Storing things like numbers or dates (or anything else that has a specific data type for it) into char/text/string causes all kinds of problems (at the very least it causes extra work), so should not be considered unless there are exceptional circumstances that mean there is technical reasoning that justifies it... and to head off potential discussions later, a lack of experience with the other data types isn't a reason to not use them (using them will give you useful experience and knowledge).

    The values in the column are from 01-Jan-2019 to 06-10-2019. I have widen to the range the the example above to see if I could get all the result. But users can actually change those dates and have values outside the actual range. Does it matter? won't the query give me everything till its greatest date if I e.g. ask for < 2050/01/01 and there is date till 2019/01/01?
    Ignoring other issues, specifying < 2050 would find values for 2019, so that part isn't a problem.

    The part that is a problem is that your values are not date based, they are just text that us humans can interpret as dates... the database system only knows that the values are text, and does an almost 'alphabetic' comparison on them.


    You should be using a DateTime based field for the values, so I recommend you add a new column for it, and then try to transfer the values across using an Update statement, eg:
    Code:
    UPDATE tableName
    SET newfieldName = DocApprovedDate
    ...note that you will probably need some kind of Cast/Convert (or something else) around DocApprovedDate, as using it directly like this will probably give errors.

    While this step is awkward, the same step would be needed to get your current code working - doing it this way instead means it is a one-off, and you wont need to do it every time you get values from the table (for this situation and others).

    After you have copied all the values across (and manually verified that they are correct), you can remove the old column and rename the new one.

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,580

    Re: SQL - Between two dates issue

    Additional to my info and Si's, I've noticed that the codepage seems more of a European- Eastern European standard. So you may also need to cast to a specific formatting - code style, else there is a change to have issues. I think 103 or 113 ?
    See here: https://docs.microsoft.com/en-us/sql...l-server-ver15
    Slow as hell.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    364

    Re: SQL - Between two dates issue

    This is the closest I can find to a version under property: 14.0.1000.169.
    Thank you both very much. I will apply the advice and will report back. I think I will probably recreate the whole table as I can see that there are other fields as well with improper datatype.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    364

    Re: SQL - Between two dates issue

    Just wanted to thank again.
    Recreated the table with proper datatypes. Also as Sapator mentioned the date format had to be yyyy-mm-dd for it to work, I formatted the date in the Windows application before running the query and things just run fine now.
    I appreciate your help very much.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width