Re: DATEADD strange results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Chris White (nothanks)
Date: 12/27/04 

Date: Mon, 27 Dec 2004 15:31:45 -0500

Ok,
So how do I ensure that I get all records for a particular month? I was
hoping to use

WHERE invoice_date BETWEEN @sdate AND @edate

which I was hoping would give me

WHERE invoice_date BETWEEN '2004-09-01 00:00:00.000' AND '2004-09-30
23:59:59.999'

but it doesn't since I can't properly calculate @edate. I can make this
formula much longer and complex and get exactly what I want, but surely
there must be an easier way. Am I just overlooking the obvious?

Should I not subtract from @edate and leave it at '2004-10-01 00:00:00.000'
and use this?

WHERE invoice_date >= @sdate AND invoice_date < @edate

That just doesn't seem consistent to me.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O3sHJ$E7EHA.2012@TK2MSFTNGP15.phx.gbl...
> Chris White wrote:
> > This is some code I've ripped from a stored procedure. I want the
> > first and last possible datetime given a Month and Year. Please
> > explain what is going on in @edate and @edate2.
> >
> > I was expecting @edate = '2004-09-30 23:59:59.999'
>
> From the documentation (BOL):
> datetime
>
> Date and time data from January 1, 1753 through December 31, 9999, to an
> accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds
> or 0.00333 seconds). Values are rounded to increments of .000, .003, or
.007
> seconds, as shown in the table.Example Rounded example
> 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
> 01/01/98 23:59:59.995,
> 01/01/98 23:59:59.996,
> 01/01/98 23:59:59.997, or
> 01/01/98 23:59:59.998 1998-01-01 23:59:59.997
> 01/01/98 23:59:59.992,
> 01/01/98 23:59:59.993,
> 01/01/98 23:59:59.994 1998-01-01 23:59:59.993
> 01/01/98 23:59:59.990 or
> 01/01/98 23:59:59.991 1998-01-01 23:59:59.990
>
>
>
>
> >
> > -----------------
> > DECLARE
> > @MTDYTD varchar(3),
> > @Month varchar(2),
> > @Year varchar(4),
> > @sdate datetime,
> > @edate datetime,
> > @edate2 datetime,
> > @tempDate datetime
> > SET @MTDYTD = 'MTD'
> > SET @Month = '9'
> > SET @Year = '2004'
> >
>
> It looks like you are needlessly complicating things here. Try this:
>
> IF @MTDYTD = 'MTD'
> SET @sdate = CAST(@Year + CAST(RIGHT('0' + @Month, 2) AS CHAR(2)) + '01'
as
> datetime)
> ELSE
> SET @sdate = CAST(@Year + '01' + '01' as datetime)
>
> IF CAST(DATEPART(m, GETDATE()) AS varchar) = @Month AND
CAST(DATEPART(yyyy,
> GETDATE()) AS varchar) = @Year
> SET @edate = getdate()
> ELSE
> SET @edate=convert(char(8),dateadd(d,-1,dateadd(m,1,@year + right('0' +
> @month,2) + '01')),112) + ' 23:59:59.997'
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Relevant Pages

  • pulling all dates within a date range
    ... write a function to pull all dates within a given date range. ... declare @Sdate as datetime ... declare @Edate as datetime ...
    (comp.databases.ms-sqlserver)
  • DATEADD strange results
    ... last possible datetime given a Month and Year. ... on in @edate and @edate2. ... GETDATE()) AS varchar) = @Year ...
    (microsoft.public.sqlserver.programming)
  • Re: Import Access records to excel (parameter is a called funct)
    ... I assume you need to return the variables sDate and eDate to the calling ... >> You need to understand that your .mdb file is a Jet database. ... >> functions nor the UDF functions in an Excel workbook. ...
    (microsoft.public.excel.programming)
  • Re: arrrggghhh!! Select statements..
    ... Note also the BETWEEN(date, sdate, edate) is an Xbase function. ... Both work in VFP but as soon as you start communicating with other SQL ... invonum linenum rptrnum product amount ...
    (microsoft.public.fox.programmer.exchange)
  • VBA Dates help
    ... Dim sdate As Date ... Dim edate As Date ... Dim wknumtot As Integer ...
    (microsoft.public.excel.programming)