Click here to Skip to main content
12,448,720 members (52,676 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL SQL-Server
I have a database table as follows..

PKDrugId - Int
DrugName - Varchar(50)
DrugType - Varchar(25)
CreationDate - Date



I am trying to generate a report which gives all the Drug Names which are entered in the current month. How should i write a query for this in SQL Server 2008?
Posted 24-Jan-13 21:48pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Select * from Drug Where Month(getdate())=Month(CreationDate) and Year(Getdate())=Year(CreationDate)
  Permalink  
v2
Comments
Varun GJ 25-Jan-13 4:05am
   
Thank you
Varun GJ 25-Jan-13 4:16am
   
How do I Get Previous month for the same scenario?
Devang Vaja 25-Jan-13 4:37am
   
Select * from Drug where case Year(getdate) when 12 then Month(getdate())-1=Month(CreationDate) and Year(getdate())-1=year(creationDate) else month(getdate())-1=month(CreationDate) and year(getdate())=year(creationDate) end
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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!
  Permalink  
v4
Comments
Varun GJ 25-Jan-13 4:06am
   
Thank You RobBranaghan
RobBranaghan 25-Jan-13 4:38am
   
You're welcome
Varun GJ 25-Jan-13 4:17am
   
How do I Get Previous month for the same scenario?
RobBranaghan 25-Jan-13 4:38am
   
See edited code above

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160811.3 | Last Updated 25 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100