Posted by brymcguire on 05/10/07 13:11

On 10 May, 13:03, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:
> > The query needs to identify all sales between the last day of the
> > previous month and going back one year.
>
> Below is one method. I generally recommend using >= and < instead of
> BETWEEN for datetime data types. This will better handle datetime values
> that include time.
>
> WHERE
> SoldDate >= CAST(CONVERT(CHAR(6), DATEADD(year, -1, DATEDIFF(day, 0,
> GETDATE())), 112) + '01' AS datetime)
> AND
> SoldDate < CAST(CONVERT(CHAR(6), DATEADD(day, 0, DATEDIFF(day, 0,
> GETDATE())), 112) + '01' AS datetime)
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> <brymcgu...@googlemail.com> wrote in message
>
> news:1178789743.579297.70040@n59g2000hsh.googlegroups.com...
>
>
>
> > Hi,
>
> > I have a requirement to design a query that identifies items sold
> > between two dates. There is a 'SoldDate' datetime field used to
> > register what date the item was sold.
>
> > The query needs to identify all sales between the last day of the
> > previous month and going back one year.
>
> > What I would like to do is to design a query / stored procedure that
> > will dynamically create the criteria to allow the client to simply run
> > the query or stored proc.
>
> > I know how to establish the last day of the previous month part, I'm
> > just not sure of how best to design the remainder of the query.
>
> > Thank in advance- Hide quoted text -
>
> - Show quoted text -


Hi Dan & Plamen,

Thanks for the solutions. Both worked great.

B

[Back to original message]