You need to use
DATEPART( datepart , date )
Some useful info on this is available at
http://msdn.microsoft.com/en-us/library/ms174420.aspx[
^]
I wasn't sure of the table name, so I used tblDrugs.
SELECT
DrugName
FROM
tblDrugs
WHERE
DATEPART(month,CreationDate) = DATEPART(month, GETDATE())
AND
DATEPART(year,CreationDate) = DATEPART(year, GETDATE())
Hope that helps you understand how to use it!
~~~Edit~~~
If you wanted to get last months data you could just subtract 1 from the month.
SELECT
DrugName
FROM
tblDrugs
WHERE
DATEPART(month,CreationDate) = DATEPART(month,DATEADD(MONTH, -1,GETDATE()) )
AND
DATEPART(year,CreationDate) = DATEPART(year, DATEADD(MONTH, -1,GETDATE()))
All the best!