Difference in Days

[batteam]

I know SQL has the Datediff function but does anyone know of a way to determine the difference in days when just using the month and day from two dates? For example, I need to know if today's date, GetDate() is within 60 days of a given past date, like 10/23/2003. Seems simple enough using DatePart but I run into problems when today is a day in December and the past date is in January, like 12/10/2013 and 01/15/2005. Then you'd have a situation like 12/10 for today, for example, then 01/15 for the past date (dropping the year). Seems there should be a way to parse out the year in two given dates then find the difference in days between those two values. Thanks for any help you can offer.

 

[SQLBill]

How about this:

IF (select datediff(day, '2005-01-01', getdate())) > 60
 PRINT 'more than 60 days'
ELSE
IF (select datediff(day, '2005-01-01', getdate())) < 60 
 PRINT 'less than 60 days'
ELSE
 PRINT '60 days'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Since I can't edit my post...here is the code showing Decemeber:

IF (select datediff(day, '2005-01-01', '2013-12-31')) > 60
 PRINT 'more than 60 days'
ELSE
IF (select datediff(day, '2005-01-01', '2013-12-31')) < 60 
 PRINT 'less than 60 days'
ELSE
 PRINT '60 days'

That returned:

more than 60 days

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Whoops...I may have misunderstood. It appears (on re-reading) that you have date values without the year. SQL Server isn't going to be able to figure those out. How does it know what the year is for each date?

12/10
01/15

Is that: 12/10/2013 and 01/15/2005 or is it 12/10/2005 and 01/15/2013, or 12/10/2013 and 01/05/2014? or something else? Since the years can be anything, SQL Server can't tell which one is later than the other. 12/10 is within 60 days of 01/15 if 01/15 is the following year.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[imex]

Not sure I understand correctly, but try to do a test with the following script:

declare @MyDate1 date, @MyDate2 date;
set @MyDate1 = '12/10/2013';
set @MyDate2 = '01/15/2005';

select 
    @MyDate1, 
    @MyDate2,
    DATEDIFF
        (DAY, 
         @MyDate1,
         case when DATEADD(YEAR, DATEDIFF(YEAR, @MyDate2, @MyDate1), @MyDate2) < @MyDate1
             then DATEADD(YEAR, DATEDIFF(YEAR, @MyDate2, @MyDate1) + 1, @MyDate2)
             else DATEADD(YEAR, DATEDIFF(YEAR, @MyDate2, @MyDate1), @MyDate2)
         end)

Hope it's useful.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[SgtJarrow]

I took a stab at your problem. If I follow it correctly, see the below scripting. Should put you one the right track to come up with a complete solution.

/********************************/
/* ASSUMES DATE FORMAT IS MM/DD */
/********************************/

--Create a table
DECLARE @DatesToReview TABLE
(
	FromDate					CHAR(5)
	, ThruDate					CHAR(5)
	, FromDateWithBogusYear		DATETIME
	, ThruDateWithBogusYear		DATETIME
)

--Populate some sample data
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('12/10', '01/15')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('01/15', '12/10')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('11/11', '01/15')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('02/10', '03/15')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('09/01', '10/15')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('03/10', '11/15')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('11/15', '03/10')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('11/15', '11/20')
INSERT INTO @DatesToReview (FromDate, ThruDate) VALUES ('11/20', '11/15')

--Update the FromDateWithBogusYear column.  We assume that if the the FromDate value is larger than the thruDate, it occurred in the previous year
UPDATE @DatesToReview
SET FromDateWithBogusYear =	(
								CASE
									WHEN CAST(SUBSTRING(FromDate, 1, 2) AS INTEGER) <= CAST(SUBSTRING(ThruDate, 1, 2) AS INTEGER) AND CAST(SUBSTRING(FromDate, 4, 2) AS INTEGER) <= CAST(SUBSTRING(ThruDate, 4, 2) AS INTEGER) THEN CAST(FromDate + '/' + SUBSTRING(CAST(DATEPART(YYYY, GETDATE()) AS CHAR(4)), 3, 2) AS DATETIME)
									ELSE CAST(FromDate + '/' + SUBSTRING(CAST(DATEPART(YYYY, DATEADD(YEAR, -1, GETDATE())) AS CHAR(4)), 3, 2) AS DATETIME)
								END
							)

--Update the ThruDateWithBogusYear column by just appending this year's date on the end
UPDATE @DatesToReview
SET ThruDateWithBogusYear = ThruDate + '/' + SUBSTRING(CAST(DATEPART(YYYY, GETDATE()) AS CHAR(4)), 3, 2)

SELECT
	FromDate
	, ThruDate
	, FromDateWithBogusYear
	, ThruDateWithBogusYear
	, CASE
		WHEN DATEDIFF(DAY, FromDateWithBogusYear, ThruDateWithBogusYear) <= 60 THEN 'Yes'
		ELSE 'No' 
	  END 'IsWithinPast60Days'
FROM @DatesToReview

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[LyndonOHRC]

Isn't it this simple?
If startMonth<=endMonth
buid startDate string using startMonth/startDay/currentYear
build endDate string using endMonth/endDay/currentYear
perform the dateDiff
else //startMonth>endMonth
buid startDate string using startMonth/startDay/currentYear
build endDate string using endMonth/endDay/(currentYear+1) perform the dateDiff
end

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[batteam]

Thanks for all your responses. I am looking at all them to see what works for me.

LyndonOHC: can your response be put into SQL.

Thanks again, I will post whatever I come up with that works.