date prob in my sql 1

[abyinsydney]

Greetings folks

i'm trying two select allt the values bewtweeen the two dates and i'm getting the results but it is incorrect .i have a query written below is this the right way of writing a query to extract information from the data base or query needs to be modifies .

The fields in which the dates are stored in the databse its property is date

my query is

Select * FROM financial_transactions_archive_extract
WHERE financial_transactions_archive_extract.account_no = '188S12' AND financial_transactions_archive_extract.date_transaction
BETWEEN '1995-04-29' && '1996-04-29'

I think itis taking the dates variable to be numbers and giving me the result .could any one thro ligth on this

 

[sedj]

Aby, Have another look - that query should be working OK. See below :

mysql> desc qqq;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(10) | YES  |     | NULL    |       |
| b     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> select * from qqq;
+-------+------------+
| a     | b          |
+-------+------------+
| one   | 2003-01-01 |
| two   | 2004-01-01 |
| two   | 2005-01-01 |
| three | 2005-01-01 |
+-------+------------+
4 rows in set (0.00 sec)

mysql> select * from qqq where b between '2003-01-01' && '2004-02-03';
+------+------------+
| a    | b          |
+------+------------+
| one  | 2003-01-01 |
| two  | 2004-01-01 |
+------+------------+

 

[abyinsydney]

thanks sedj
for the date stuff
any idea how to find the values between two strings

Select * FROM financial_transactions_archive_extract
WHERE financial_transactions_archive_extract.account_no = '188S12' AND financial_transactions_archive_extract.date_transaction
BETWEEN 'system' && 'system'

do in have to convert these strings to it ASCII VALUES THEN PUT THOOSE ASCII VALUES INSTEAD OF SYSTEM AND SYSTEM1 OR THE ABOVE QUERY WOULD WORK

 

[abyinsydney]

GREETINGS SEDJ

WITH THE DATE PROBLEM IF YOU DO

Select * FROM financial_transactions_archive_extract
WHERE financial_transactions_archive_extract.account_no = '188S12' AND financial_transactions_archive_extract.date_transaction
BETWEEN '1995-04-29' && '1996-04-29'

IF YOU CHANGE THE FIRST DATE IT STILL GIVES THE SAME RESULT SEEMS LIKE IT IS GIVING THE RESULTS BETWEEN NUMBERS.PLEASE VERIFY CHANGE 1995-04-29 TO 1995-04-24 IT WOULD GIVE ME RESULTS FOR DATES LATER THAN 1995-04-24 ONLY

 

[sedj]

It matters which way round you have the dates :

mysql> select * from qqq where b BETWEEN '1995-04-29' && '1996-04-24'
    -> ;
+------+------------+
| a    | b          |
+------+------------+
| 333  | 1995-04-29 |
+------+------------+
1 row in set (0.00 sec)

mysql> select * from qqq where b BETWEEN '1995-04-24' && '1996-04-29'
    -> ;
+------+------------+
| a    | b          |
+------+------------+
| 333  | 1995-04-29 |
| 444  | 1995-04-24 |
+------+------------+