Date range

[viscorus2004]

Hy sorry for my bad english

I have transaksi_pembayaran table and have a tgl_tran field, tgl_tran is a date but has type varchar, how can I get date range in mysql?

date range like this :

14/2/2005 to 27/2/2005 (d/m/y)

[pritaeas]

You can use str_to_date().

Found here: http://www.webdeveloper.com/forum/showthread.php?t=108647

[cmgarnett]

Have you tried something like:-

SELECT * FROM `transaksi_pembayaran` WHERE STR_TO_DATE(`tgl_tran`) BETWEEN '2005/02/14' AND '2005/02/27';

I believe that MySQL always works with y/m/d dates.

[shasank]

Hello viscorus2004,

you can also try the below query, since the date is a stream of characters... you can enclose it in single quote...

select * from transaksi_pembayaran where tgl_tran between '14/2/2005' and '27/2/2005';

It's working well for me, i think you can also check this...

[cmgarnett]

The problem with using the date as a string is that '19/3/2005' would fall into your range as would '23/1/1998'. It would be checking the range from an alphabetic point of view rather than a date one. Unless I've missed something here and I'm always happy to learn.

[shasank]

thanks for your concern cmgarnett,

But since it was just the matter of retrieving records for that particular date range, i think usage of string to date function would be very much complicated for the newbie.... It was just a simple query then y we need to complicate it!...

Please reply for this and let me know if anything am missing on this... Thanks Shasank.

[cmgarnett]

Hi shasank

It's not a case of complicating things, but making them work. Comparing dates as if they were strings cannot work because they would be treated as a string and sorted alphabetically.

If the range is between '14/2/2005' and '27/2/2005' and you find a record with a stringed date of '20/1/1966' then the alphabetic comparisson would go like this...

'20' is greater then '14' and so the record comes after the start of the range.
'20' is less than '27' and so the record comes before the end of the range.
If the record is after the start and before the end of the range then it must be in the range and so you will have a record that, to the human eye, is not in the range; but to the computer's brain it is in the range.

There is no point in continuing with the compare as we already know that the record is in the range. So the month an year elements are ignored.

If you swapped things around and used strings that were in 'YYYY/M/DD' format, then the string compares would work - until you found an input record with a date in 'YYYY/MM/DD' or 'YYYY/M/D' format.

Using the STR_TO_DATE reduces the complexity by introducing a standard date format.

Sorry, I don't mean to rant but sorting things that are not strings as if they were is frought with problems and worth avoiding.

[maks91]

How can i select a date range if its a php timestamp?

[viscorus2004]

This is work for me thank you :D

SELECT tgl_tran FROM transaksi_pembayaran WHERE STR_TO_DATE(tgl_tran,'%d/%m/%Y') BETWEEN '14/2/2005' AND '27/2/2005'

[tyson.crouch]

How can i select a date range if its a php timestamp?

G'day maks91,

I've had to do a little research to check exactly how this is done, and as far as i can tell, it works the same is it does with other dates.

Given a table:

CREATE TABLE student (
`student_ID`  INT  NOT NULL  AUTO_INCREMENT,
`student_DOB`  TIMESTAMP  NOT NULL,
PRIMARY KEY (`student_ID`)
)ENGINE=INNODB;

And some sample data;

Insert Into `student` Values(NULL, 'Stan', '1984-10-27');
Insert Into `student` Values(NULL, 'Kyle', '1985-04-19');
Insert Into `student` Values(NULL, 'Kenny', '1986-11-16');
Insert Into `student` Values(NULL, 'Cartman', '1987-10-22');

Finally the SELECT query:

SELECT * 
FROM `student`
WHERE `student_DOB` Between '1984-01-01' AND '1988-1-1'