Wrox Programmer Forums Date Calculations
 |
 SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

March 24th, 2006, 11:25 AM
 scourge Registered User Join Date: Feb 2006 Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts
Date Calculations

I need help. We are trying to calculate the difference between 2 dates. Using DateDiff, and yyyy only gives us the difference in years. We may and will have the situation where '03/20/2003' and '03/24/2006' is greater than 3 years. By using DateDiff with the 'yyyy' option, we are not greater than 3 years.
Is there a way to calcuate the difference in 'mm/dd/yyyy' and get a correct solution?

Len

March 24th, 2006, 03:29 PM
 rstelma Friend of Wrox Join Date: Jul 2003 Posts: 599 Thanks: 6 Thanked 3 Times in 3 Posts

No exactly sure what you're trying to do. But here's a way to do what I'm not sure you're trying to do.

Declare @years varchar(5), @months varchar(5), @days varchar(5), @thedatediff varchar(15)

Set @years = DateDiff(year,'03/20/2003','03/24/2006')
Set @months = DateDiff(month, '03/20/2003','03/24/2006') % 12
Set @days = DateDiff(day, '03/20/2003','03/24/2006') % 365 -1

Select years = @years, months = @months, days = @days

What exactly would be the correct to solution to the particular problem you stated? '00/04/0000' ??? as you say 'mm/dd/yyyy'

??

Richard

May 9th, 2006, 01:59 AM
 Peso Friend of Wrox Join Date: May 2006 Posts: 246 Thanks: 0 Thanked 0 Times in 0 Posts

Well, actually you're wrong. The dates you provided is not GREATER than 3 years. It is 3 years and some days.

What you need is GREATER than OR EQUAL to 3 years.

use >=

May 9th, 2006, 02:10 AM
 Peso Friend of Wrox Join Date: May 2006 Posts: 246 Thanks: 0 Thanked 0 Times in 0 Posts

select convert(int, datediff(dd, '03/20/2003', '03/24/2006')-dateadd(dd, 0, '1903-01-01'))

when the two dates are the same, the result is 1. if you want the result to be 0 when the two dates are the same, change 0 to 1 in the dateadd function.

the result is the number of days exceeding three years, including scot years.

May 9th, 2006, 06:03 PM
 robprell Friend of Wrox Join Date: Aug 2004 Posts: 385 Thanks: 0 Thanked 0 Times in 0 Posts

You may just want to use the date add function, adding three years to one date and check if the other data is greater than the adjusted date.

May 10th, 2006, 10:54 AM
 rstelma Friend of Wrox Join Date: Jul 2003 Posts: 599 Thanks: 6 Thanked 3 Times in 3 Posts

Scourge,

Are you still there? Did you dissappear?

Peso and robprell. Thanks for your replies.

Richard
SQL Search & Rescue

May 10th, 2006, 04:25 PM
 robprell Friend of Wrox Join Date: Aug 2004 Posts: 385 Thanks: 0 Thanked 0 Times in 0 Posts

we have hijacked the thread, lol.

May 10th, 2006, 06:33 PM
 rstelma Friend of Wrox Join Date: Jul 2003 Posts: 599 Thanks: 6 Thanked 3 Times in 3 Posts

Cool... date calculations always fascinated (and troubled) me!

How many days till the end of time? Well... OK... how many pay day Fridays?

May 11th, 2006, 03:30 AM
 manudutt Authorized User Join Date: May 2006 Posts: 13 Thanks: 0 Thanked 0 Times in 0 Posts

declare @m int, @y int , @d int , @dat datetime

set @dat = '2004-12-20'
select @y=datediff(year,@dat,getdate())
select @m=datediff(month,@dat,getdate())

set @m=@m-(@y*12)
if @m < 0
begin
set @y=@y-1
set @m=12+@m
end

--getting days
set @dat = dateadd(year, @y, @dat)
set @dat = dateadd(month, @m, @dat)
set @d = datediff(day,@dat,getdate())

if @d < 0
begin
set @m=@m-1
set @d=30+@d
end

if @m < 0
begin
set @y=@y-1
set @m=12+@m
end

select @y 'year' , @m 'month', @d 'day'
---------------------------------------------
this will work not exactly accurate but nearly (difference can be in +/- 1 day)

May 11th, 2006, 05:08 AM
 Peso Friend of Wrox Join Date: May 2006 Posts: 246 Thanks: 0 Thanked 0 Times in 0 Posts

Liked robprell's solution. Sometimes you can't see the tree for the forest.

IF DATEADD(yyyy, 3, '03/25/2003') >= '03/24/2006'
PRINT 'More than three years'
ELSE
PRINT 'Have not reached three years'

 Similar Threads Thread Thread Starter Forum Replies Last Post Date Calculations edove ASP.NET 2.0 Basics 0 March 30th, 2007 07:05 AM Positioning Calculations rit01 ASP.NET 1.x and 2.0 Application Design 3 February 22nd, 2006 03:10 PM Date Calculations Stu_morris40 Infopath 0 November 1st, 2005 11:53 AM Wierd Date Calculations Steven Access 2 December 16th, 2003 07:44 PM Date Calculations mvollmer Classic ASP Databases 4 October 15th, 2003 10:48 PM