SQL Query with Current date + 30 days

27 May 2005 11:11 AM

JP SIngh

I need to find out all the records where a particular field is within 30
days of current date.

Can someone help me with the SQL please

I am new to SQL programming. The statement below used to work with Access
2000

     sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
Contracts.Subject, Contracts.contractenddate "
      sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
Between Date() -30 And Date() +30)) "
      sql = sql & " ORDER BY Contracts.contractenddate DESC; "

thanks
Jas

27 May 2005 11:31 AM

Christian Donner

"JP SIngh" schrieb:

> I need to find out all the records where a particular field is within 30
> days of current date.
> Can someone help me with the SQL please
> I am new to SQL programming. The statement below used to work with Access
> 2000
>      sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate "
>       sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
> Between Date() -30 And Date() +30)) "
>       sql = sql & " ORDER BY Contracts.contractenddate DESC; "
>
> thanks
> Jas

select top 4 Contracts.Id, Contracts.ContractNumber,
                 Contracts.Subject, Contracts.contractenddate
   FROM Contracts
   WHERE Contracts.contractenddate
   Between Dateadd('dd', -30, GetDate())
   And Dateadd('dd', 30, GetDate())
   ORDER BY Contracts.contractenddate DESC

27 May 2005 11:31 AM

John Bell

Hi

The Getdate() function will get you the current date/time. Datediff will
give you the number of date and time boundaries crossed between two specified
dates for a give datepart.

See Books online for more on date/time functions or at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp

John

Show quoteHide quote

"JP SIngh" wrote:

> I need to find out all the records where a particular field is within 30
> days of current date.
>
> Can someone help me with the SQL please
>
> I am new to SQL programming. The statement below used to work with Access
> 2000
>
>
>      sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate "
>       sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
> Between Date() -30 And Date() +30)) "
>       sql = sql & " ORDER BY Contracts.contractenddate DESC; "
>
> thanks
> Jas
>
>
>

27 May 2005 11:35 AM

GilaMonster

SELECT top 4 Contracts.Id, Contracts.ContractNumber,
Contracts.Subject, Contracts.contractenddate
FROM Contracts WHERE Contracts.contractenddate
Between DATEADD(dd,-30,GetDate()) And DATEADD(dd,30,GetDate())
ORDER BY Contracts.contractenddate DESC

Bear in mind that getdate is a full datetime and if it is important that the
time not be considered you need to do a bit more, like follows

SELECT top 4 Contracts.Id, Contracts.ContractNumber,
Contracts.Subject, Contracts.contractenddate
FROM Contracts WHERE Contracts.contractenddate
Between CONVERT(VARCHAR(8),DATEADD(dd,-30,GetDate()),112) And
CONVERT(VARCHAR(8),DATEADD(dd,30,GetDate()),112) ORDER BY
Contracts.contractenddate DESC

--
Gail Shaw (MCSD)
http://gail.rucus.net/

Show quoteHide quote

"JP SIngh" wrote:

> I need to find out all the records where a particular field is within 30
> days of current date.
>
> Can someone help me with the SQL please
>
> I am new to SQL programming. The statement below used to work with Access
> 2000
>
>
>      sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate "
>       sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
> Between Date() -30 And Date() +30)) "
>       sql = sql & " ORDER BY Contracts.contractenddate DESC; "
>
> thanks
> Jas
>
>
>

27 May 2005 1:09 PM

John Bell

Please ignore - refresh problem!!!

Show quoteHide quote

"GilaMonster" wrote:

>
> SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate
> FROM Contracts WHERE Contracts.contractenddate
> Between DATEADD(dd,-30,GetDate()) And DATEADD(dd,30,GetDate())
> ORDER BY Contracts.contractenddate DESC
>
> Bear in mind that getdate is a full datetime and if it is important that the
> time not be considered you need to do a bit more, like follows
>
> SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate
> FROM Contracts WHERE Contracts.contractenddate
> Between CONVERT(VARCHAR(8),DATEADD(dd,-30,GetDate()),112) And
> CONVERT(VARCHAR(8),DATEADD(dd,30,GetDate()),112) ORDER BY
> Contracts.contractenddate DESC
>
>
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
>
>
> "JP SIngh" wrote:
>
> > I need to find out all the records where a particular field is within 30
> > days of current date.
> >
> > Can someone help me with the SQL please
> >
> > I am new to SQL programming. The statement below used to work with Access
> > 2000
> >
> >
> >      sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> > Contracts.Subject, Contracts.contractenddate "
> >       sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
> > Between Date() -30 And Date() +30)) "
> >       sql = sql & " ORDER BY Contracts.contractenddate DESC; "
> >
> > thanks
> > Jas
> >
> >
> >

27 May 2005 1:07 PM

John Bell

You posted exactly the same thing earlier. Please see the other replies.

John

Show quoteHide quote

"JP SIngh" wrote:

> I need to find out all the records where a particular field is within 30
> days of current date.
>
> Can someone help me with the SQL please
>
> I am new to SQL programming. The statement below used to work with Access
> 2000
>
>
>      sql = "SELECT top 4 Contracts.Id, Contracts.ContractNumber,
> Contracts.Subject, Contracts.contractenddate "
>       sql = sql & " FROM Contracts WHERE (((Contracts.contractenddate)
> Between Date() -30 And Date() +30)) "
>       sql = sql & " ORDER BY Contracts.contractenddate DESC; "
>
> thanks
> Jas
>
>
>