Wrox Programmer Forums
|
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
 
Old March 24th, 2006, 11:25 AM
Registered User
 
Join Date: Feb 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?

Thanks in Advance
Len

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

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

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

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 >=

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

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.

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

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.

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

Scourge,

Are you still there? Did you dissappear?

Peso and robprell. Thanks for your replies.

Richard
SQL Search & Rescue

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

we have hijacked the thread, lol.

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

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

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

 
Old May 11th, 2006, 03:30 AM
Authorized User
 
Join Date: May 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to manudutt
Default

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)


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

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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.