get current month and year's data

• PSB

  SSChampion

  Points: 13195

  More actions

  February 16, 2011 at 11:26 am

  #245662

  Hi,

  I have a tanle called X and have Fields like

  B network DateLoaded

  11 c net 2011-01-01

  12 c net 2011-01-01

  12 c net 2011-02-01

  13 e net 2011-02-01

  14 f net 2011-02-01

  WHEN I execute the sp for the current month e.g now its feb, then I sp should only pull

  B network DateLoaded

  12 c net 2011-02-01

  13 e net 2011-02-01

  14 f net 2011-02-01

  If the current month is JAn then the SP should pull

  B network DateLoaded

  11 c net 2011-01-01

  12 c net 2011-01-01

  Please help

  Thanks.

• Kiara

  SSCarpal Tunnel

  Points: 4185

  More actions

  February 16, 2011 at 11:44 am

  #1287830

  Not sure what version of SQL you are on, so I'm assuming SQL 2008.

  This is only one possible option - there are certainly others, but hopefully this will get you started.

  select B, network, DateLoaded from X

  where DateLoaded >= DATEADD(day,-DATEPART(day,getdate())+1 , cast(getdate() as date))

  and DateLoaded < DATEADD(month, 1, DATEADD(day,-DATEPART(day,getdate())+1 , cast(getdate() as date)) )

  -Ki

• PSB

  SSChampion

  Points: 13195

  More actions

  February 16, 2011 at 12:16 pm

  #1287861

  I a using the following in the where clause

  DATEPART(m, ALLPOINT.dateloaded) = DATEPART(m, GetDate()) AND DATEPART(yyyy, ALLPOINT.dateloaded) = DATEPART(yyyy, GetDate())

  but it is not pulling records which are only in the current month.

  that is it is only pulling

  12 c net 2011-02-01

  instead of

  12 c net 2011-02-01

  13 e net 2011-02-01

  14 f net 2011-02-01

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 16, 2011 at 12:18 pm

  #1287864

  Try this

  CREATE TABLE #T(B INT,network VARCHAR(20),DateLoaded DATETIME)

  INSERT INTO #T

  SELECT 11, 'c net', '2011-01-01' UNION ALL

  SELECT 12, 'c net', '2011-01-01' UNION ALL

  SELECT 12, 'c net', '2011-02-01' UNION ALL

  SELECT 13, 'e net', '2011-02-01' UNION ALL

  SELECT 14, 'f net', '2011-02-01'

  Use this code to extract data for any month

  --==To get datafor any month

  --==Run as dbo.EventsforgivenMonth -returns this months data

  --==Run as dbo.EventsforgivenMonth '2011-01-01' will return data

  --==for January 2011

  CREATE PROC dbo.EventsforgivenMonth

  @D DATETIME = NULL

  AS

  SET @D =ISNULL( @D, GETDATE() )

  SELECT B, Network, DateLoaded FROM #T

  WHERE DATEPART(mm,DateLoaded)= DATEPART(mm,@D)

  ORDER BY DATEPART(mm,DateLoaded)

  Results for running as dbo.EventsforgivenMonth

  12c net2011-02-01 00:00:00.000

  13e net2011-02-01 00:00:00.000

  14f net2011-02-01 00:00:00.000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Kiara

  SSCarpal Tunnel

  Points: 4185

  More actions

  February 16, 2011 at 12:21 pm

  #1287866

  PSB (2/16/2011)

  ---

  I a using the following in the where clause

  DATEPART(m, ALLPOINT.dateloaded) = DATEPART(m, GetDate()) AND DATEPART(yyyy, ALLPOINT.dateloaded) = DATEPART(yyyy, GetDate())

  but it is not pulling records which are only in the current month.

  that is it is only pulling

  12 c net 2011-02-01

  instead of

  12 c net 2011-02-01

  13 e net 2011-02-01

  14 f net 2011-02-01

  What's the rest of the query?

  -Ki

• PSB

  SSChampion

  Points: 13195

  More actions

  February 16, 2011 at 12:32 pm

  #1287873

  Thanks it worked perfectly!