help for query, select records in a range of date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help for query, select records in a range of date

Author

Topic

arrakis
Starting Member

5 Posts

Posted - 2009-02-04 : 14:39:11

Hello all, I hope someone can give a hint on how to solve that, cause I'm going out of mind

I have like 4500 records in a table. Each record has data and also 3 fields for the date: "Day", "Month", "Year" (unfortunately there isn't a "datetime" field).

I have to do a query that returns all the records between the actual day (today) and 7 days before, but I'm getting in troubles with the query (since I'm not an expert).

In the beginning I did simply:

SELECT WHERE .... AND Day <= $today AND Day > $today - 7 ...

but of course works only if the actual day is >= 8 (less than 8 the result will be 0 or negative number).

Anyone has a hint on how to setup the proper query for that?

Thanks a lot in advance.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-04 : 15:47:13

Combine the 3 columns to arrive at a datetime, and then do a match for last 7 days.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 16:06:36

Try this.

SELECT ...WHERE (CAST(RTRIM(CONVERT(VARCHAR(8),GETDATE(),112)) AS INT) - CAST( YEAR + MONTH + DAY AS INT)) <= 7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:11:29

[code]SELECT *
FROM Table1
WHERE DATEDIFF(DAY, DATEADD(MONTH, 12 * colYear - 22801 + colMonth, colDay - 1), GETDATE() <= 7[/code]

E 12°55'05.63"
N 56°04'39.26"

arrakis
Starting Member

5 Posts

Posted - 2009-02-05 : 03:17:20

Thank you guys, but unfortunately your solutions are not working, I tried in many different ways but I get sintax errors (that I don't really understand).

Do you think that is there a more simple way for the query?

By the way I use MySQL 4.1.14

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 03:26:29

[code]SELECT *
FROM Table1
WHERE DATEDIFF(DAY, DATEADD(MONTH, 12 * colYear - 22801 + colMonth, colDay - 1), GETDATE() BETWEEN 0 AND 7[/code]
You will have to find the correct syntax for MySQL.

E 12°55'05.63"
N 56°04'39.26"

arrakis
Starting Member

5 Posts

Posted - 2009-02-05 : 05:07:32

quote:
Originally posted by Peso

SELECT *
FROM Table1
WHERE DATEDIFF(DAY, DATEADD(MONTH, 12 * colYear - 22801 + colMonth, colDay - 1), GETDATE() BETWEEN 0 AND 7

You will have to find the correct syntax for MySQL.

E 12°55'05.63"
N 56°04'39.26"

Thank you Paso, but seems that I'm not able to make it work... thanks anyway.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 05:14:12

Works for me...

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		colYear SMALLINT NOT NULL,
		colMonth TINYINT NOT NULL,
		colDay TINYINT NOT NULL
	)

-- Populate sample data
DECLARE	@Index SMALLINT

SET	@Index = 0

WHILE @Index <= 365
	BEGIN
		INSERT	@Sample
		SELECT	DATEPART(YEAR, DATEADD(DAYOFYEAR, @Index, '20090101')),
			DATEPART(MONTH, DATEADD(DAYOFYEAR, @Index, '20090101')),
			DATEPART(DAY, DATEADD(DAYOFYEAR, @Index, '20090101'))

		SET	@Index = @Index + 1
	END

-- Display the result
SELECT	*
FROM	@Sample
WHERE	DATEDIFF(DAY, DATEADD(MONTH, 12 * colYear - 22801 + colMonth, colDay - 1), GETDATE()) BETWEEN 0 AND 6

The resultset I get today is

colYear colMonth colDay
------- -------- ------
   2009        1     30
   2009        1     31
   2009        2      1
   2009        2      2
   2009        2      3
   2009        2      4
   2009        2      5

E 12°55'05.63"
N 56°04'39.26"

arrakis
Starting Member

5 Posts

Posted - 2009-02-05 : 08:29:44

Hello Peso, you are very gentle to help me, I wasn't able to convert your script to mysql syntax (I really tried a lot reading on line mysql manuals also).

But now I decided to make a conversion, instead of having 3 fields for the date, I added a "date" field and with a php script I converted all the date records that I had in the date field, formatted like that: YYYY-MM-DD.

Now I think all will be easied (actually I should have done that before but I wasn't sure how to).

arrakis
Starting Member

5 Posts

Posted - 2009-02-05 : 10:16:08

OK, after I created the date field, the proper query to make everything work is the following:

SELECT * FROM Table WHERE DateField BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()

Everything works now!

Thanks again :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources