DateDiff

estefex · #1 (**permalink**) 04-15-04, 15:39

I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.

I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.

Brett Kaiser · #2 (**permalink**) 04-15-04, 16:07

The concept of what a month is, is funny....you'd be better of if you could pick a fixed number of days

It's like month is nondetermenistic, where days are determenistic..

Never read that anywhere I don't think (damn I hate when that happens)

Code:

USE Northwind
GO

CREATE TABLE myTable99(myMonth99 int, myDay99 int, myYear99 int)
GO

INSERT INTO myTable99(myMonth99, myDay99, myYear99)
SELECT 1,1,2003 UNION ALL
SELECT 2,1,2003 UNION ALL
SELECT 3,1,2003 UNION ALL
SELECT 4,1,2003 UNION ALL
SELECT 4,15,2003 UNION ALL
SELECT 4,30,2003 UNION ALL
SELECT 5,1,2003 UNION ALL
SELECT 6,1,2003 UNION ALL
SELECT 7,1,2003 UNION ALL
SELECT 8,1,2003 UNION ALL
SELECT 9,1,2003 UNION ALL
SELECT 10,1,2003 UNION ALL
SELECT 11,1,2003 UNION ALL
SELECT 12,1,2003
GO


SELECT * FROM myTable99
 WHERE	DATEDIFF(mm,	
	     CONVERT(datetime,
	     CONVERT(varchar(4),myYear99)
	+'/'+CONVERT(varchar(4),myMonth99)
	+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 12
GO

DROP TABLE myTable99
GO

estefex · #3 (**permalink**) 04-15-04, 16:15

Yeah, I see what you mean with the moth thing being funny. I figured out another way where I can do it. If I leave the date fixed where it displayes the year, month and day, I can accomplish what i have set out to do. This is what worked for me.

select * from podinrh
where date_rcvd between getdate() - 365 and getdate()
order by date_rcvd

I will also try out the script that you have provided me with Brett. Thanks for all the help you all provide.

Brett Kaiser · #4 (**permalink**) 04-15-04, 16:19

Yeah thats days...

Works much better

Code:

SELECT * FROM myTable99
 WHERE	DATEDIFF(dd,	
	     CONVERT(datetime,
	     CONVERT(varchar(4),myYear99)
	+'/'+CONVERT(varchar(4),myMonth99)
	+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 365
GO

Brett Kaiser · #5 (**permalink**) 04-15-04, 16:20

Quote:

Originally posted by estefex
I will also try out the script that you have provided me with Brett.

You know to just cut and paste it in to QA right?

estefex · #6 (**permalink**) 04-15-04, 16:31

Brett, It works fine too. I will have to see what format they are going to want to stick to. I am thinking they are going to want it with the date broken bown into columns like you did. I appreciate you help.

blindman · #7 (**permalink**) 04-15-04, 16:43

The Devil is in the details. To handle leap years, you might want to use this instead:

select * from podinrh
where date_rcvd between dateadd(year, -1, getdate()) and getdate()
order by date_rcvd

Also remember that the BETWEEN operator is inclusive. If your data is stored as whole dates (without the time of day), then you may end up excluding data from the first day or including an extra 24 hours at the end.

For date comparisons, I use this instead of BETWEEN:

select * from podinrh
where date_rcvd > dateadd(year, -1, getdate()) and date_rcvd <= getdate()
order by date_rcvd

estefex · #8 (**permalink**) 04-15-04, 17:36

Thanks for the tipp because i was useing the time with the date as well. The number of records returned is a but greater than when I used the between statement.

Internet Services

Web Development

Digital Marketing

Consumer Tech