Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2012
    Posts
    81

    Unanswered: Help with brilliant database dates query

    Hi all,

    I will like to know how to create a query to get records between two dates. For example i will like to get records between 05-09-12 and 08-09-12. I am using one date field from the form. So far i can get records between these dates using < and > but it excludes records from the first date and the last date above and only returns records between.

    HELP PLEASE!!!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You can either use the between operator:
    Code:
    select
    from the_table
    where the_date_column between date '2012-09-05' and date '2012-09-08'
    Or use >=, <= operators
    Code:
    select *
    from the_table
    where the_date_column >= date '2012-09-05' 
      and the_date_column <= date '2012-09-08'
    Both statements specify the date value using standard ANSI SQL syntax.

    If your DBMS (which you failed to mention) does not support that you have to use the DBMS specific syntax.

  3. #3
    Join Date
    Jan 2012
    Posts
    81
    Quote Originally Posted by shammat View Post
    You can either use the between operator:
    Code:
    select
    from the_table
    where the_date_column between date '2012-09-05' and date '2012-09-08'
    Or use >=, <= operators
    Code:
    select *
    from the_table
    where the_date_column >= date '2012-09-05' 
      and the_date_column <= date '2012-09-08'
    Both statements specify the date value using standard ANSI SQL syntax.

    If your DBMS (which you failed to mention) does not support that you have to use the DBMS specific syntax.
    Thanks very much .... will try and see if it works!

  4. #4
    Join Date
    Jan 2012
    Posts
    81
    Quote Originally Posted by shammat View Post
    You can either use the between operator:
    Code:
    select
    from the_table
    where the_date_column between date '2012-09-05' and date '2012-09-08'
    Or use >=, <= operators
    Code:
    select *
    from the_table
    where the_date_column >= date '2012-09-05' 
      and the_date_column <= date '2012-09-08'
    Both statements specify the date value using standard ANSI SQL syntax.

    If your DBMS (which you failed to mention) does not support that you have to use the DBMS specific syntax.
    BTW i did mention DBMS, its brilliant database. How can i perform this using a query?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Trent View Post
    BTW i did mention DBMS, its brilliant database
    Sorry, I didn't realize "brilliant" was the name of the database.

    How can i perform this using a query?
    What do you mean?
    I showed you a query.

  6. #6
    Join Date
    Jan 2012
    Posts
    81
    Quote Originally Posted by shammat View Post
    Sorry, I didn't realize "brilliant" was the name of the database.

    What do you mean?
    I showed you a query.
    Please see the attached photos.
    Attached Thumbnails Attached Thumbnails 1.png   2.png  

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Sorry, no idea. I don't know that GUI

  8. #8
    Join Date
    Jan 2012
    Posts
    81
    ok thanks!

  9. #9
    Join Date
    Oct 2011
    Posts
    18
    Because "more then" and "less then" does not include the preset, it only shows more then and less then. If you want to include the presets as well to show in your query, then either before you call query, or in the query its self, use math formula to subtract 1 day from your "more then" preset and add 1 day to your "less then" preset......

  10. #10
    Join Date
    Jun 2012
    Posts
    163
    Provided Answers: 1
    Dear Trent:

    I hope this message did not gets late...

    i am developing an application ( a big one) in brilliant, i love it... and i hate it


    days ago i got the same problem this is what worked to me.

    1. check that you are storing the dates in a format valid to do math operations insted of text (i got that problem)

    2. check that the field where you are storing the dates is a math field and not a text field

    3. at the moment you are making the query, use this "stupid" tip... the query ask you to compare two things.... so the first expresion to compare has to be a variable, and the second expresion to compare most to be a formula (a math formula), and inside the formula you can use everything (fields, operations, variables etc)

    i don`t know why it works this way, but i was three days playing around until i got into this solution. (i has to be a bug)


    it it worked for you let me know my mail is ferslash@hotmail.com

    ****
    another note: i had another problem regarding dates comparison, and i used the above solution, but did'nt worked so, i stared a new scrip, just using diferent variable names and it worked... another bug i guess)

  11. #11
    Join Date
    Jan 2012
    Posts
    81
    Quote Originally Posted by singsteel View Post
    Because "more then" and "less then" does not include the preset, it only shows more then and less then. If you want to include the presets as well to show in your query, then either before you call query, or in the query its self, use math formula to subtract 1 day from your "more then" preset and add 1 day to your "less then" preset......
    OK Singsteel,

    Firstly, thank you very much for your response. Makes sense but i just don't know how to do that. What math formula can i use to subtract a day and add a day respectively. Can you provide an example please. Thnks!

  12. #12
    Join Date
    Jan 2012
    Posts
    81
    Quote Originally Posted by ferslash View Post
    Dear Trent:

    I hope this message did not gets late...

    i am developing an application ( a big one) in brilliant, i love it... and i hate it


    days ago i got the same problem this is what worked to me.

    1. check that you are storing the dates in a format valid to do math operations insted of text (i got that problem)

    2. check that the field where you are storing the dates is a math field and not a text field

    3. at the moment you are making the query, use this "stupid" tip... the query ask you to compare two things.... so the first expresion to compare has to be a variable, and the second expresion to compare most to be a formula (a math formula), and inside the formula you can use everything (fields, operations, variables etc)

    i don`t know why it works this way, but i was three days playing around until i got into this solution. (i has to be a bug)


    it it worked for you let me know my mail is ferslash@hotmail.com

    ****
    another note: i had another problem regarding dates comparison, and i used the above solution, but did'nt worked so, i stared a new scrip, just using diferent variable names and it worked... another bug i guess)
    Thanks for your response bro but singsteel solutions appears easier to perform as the fields are already date fields. Thanks again!

  13. #13
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    i has to be a bug
    ...
    another bug i guess
    Yup, both are "bugs" in the code.

    It is most likely that the product performs as advertised. If you can deomonstrate an actual problem with the product, you need to report this to the vendor.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by papadi View Post
    If you can deomonstrate an actual problem with the product, you need to report this to the vendor.
    That's a great idea, but there's a minor problem that lies in the implementation... The development/support team for Brilliant was last heard from in mid-2009, and their web page was last updated in 2010. I haven't gotten any response from the sales department, although I've heard that the automated purchase process still works.

    As far as I know, the Brilliant Database is effectively an "abandoned propoerty" that still collects rent from customers that volunteer payments.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Oct 2011
    Posts
    18

    Bugs........?!

    Quote Originally Posted by ferslash View Post
    Dear Trent:

    I hope this message did not gets late...

    i am developing an application ( a big one) in brilliant, i love it... and i hate it


    days ago i got the same problem this is what worked to me.

    1. check that you are storing the dates in a format valid to do math operations insted of text (i got that problem)

    2. check that the field where you are storing the dates is a math field and not a text field

    3. at the moment you are making the query, use this "stupid" tip... the query ask you to compare two things.... so the first expresion to compare has to be a variable, and the second expresion to compare most to be a formula (a math formula), and inside the formula you can use everything (fields, operations, variables etc)

    i don`t know why it works this way, but i was three days playing around until i got into this solution. (i has to be a bug)


    it it worked for you let me know my mail is ferslash@hotmail.com

    ****
    another note: i had another problem regarding dates comparison, and i used the above solution, but did'nt worked so, i stared a new scrip, just using diferent variable names and it worked... another bug i guess)
    Up till now I haven't come across any bugs in this dBase, maybe a few "shortfalls" and these can be easily overcomed by a bit of scripting....
    So far it has done for me everything I wanted it to do... I think it's like its name says "Brilliant".....

Posting Permissions

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