|
|
Posted by Dan Guzman on 05/10/07 12:03
> 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
<brymcguire@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
>
Navigation:
[Reply to this message]
|