Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL parameter for Previous Month's Date

Posted on 2013-12-20
8
Medium Priority
?
325 Views
Last Modified: 2013-12-20
I am trying to get the correct syntax to filter my selections for the previous month's date based on today's date.

Attached is picture of the query code and the PeriodDateTime field (mm-dd-yyyy hh:mm)

I can hard code the month end to say 11/30/13 and no problem.  I just cannot get the correct DateAdd or DateDiff syntax to make it variable.

Thanks

Glen
select.jpg
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39732275
this will give you previous month based on current date

dateadd(m,-1,getdate())

Open in new window

0
 

Author Comment

by:GPSPOW
ID: 39732286
I tried this one before and I do not get any data back.

Glen
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39732292
past your current query in and ill have a look
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 11

Accepted Solution

by:
Simone B earned 2000 total points
ID: 39732296
WHERE (YEAR(DATEADD(m,-1,GETDATE())) = YEAR(PeriodDateTime)
AND MONTH(DATEADD(m,-1,GETDATE())) = MONTH(PeriodDateTime))
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39732302
You're not getting any data because your period dates are all on the last day of the month. When this is the case, you need to filter on the month and year only, ignoring the day and time.
0
 

Author Comment

by:GPSPOW
ID: 39732307
SELECT TOP (100) percent [SourceID]
      ,[VisitID]
      ,[PeriodDateTime]
      ,[BillingID]
      ,[AgencyID]
      ,[AgencyName]
      ,[AnyClDisExemptions]
      ,[ArAgeDateTime]
      ,[ArChgTotal]
      ,[Balance]
      ,[BarStatus]
      ,[BdAgeDateTime]
      ,[BillRuleID]
      ,[ClPendingCharges]
      ,[ClUnappliedCredits]
      ,[ClientID]
      ,[Contract1stPmtDtDateTime]
      ,[ContractAmount]
      ,[ContractDateTime]
      ,[ContractPaid]
      ,[CorpID]
      ,[CorpName]
      ,[FeeScheduleID]
      ,[FeeScheduleName]
      ,[InsuranceBalance]
      ,[LastBillTxn]
      ,[LastPayDateTime]
      ,[LastPostTxn]
      ,[LastStTxn]
      ,[LastTxn]
      ,[PtBalance]
      ,[PtType]
      ,[StmtGrpID]
      ,[TypeID]
      ,[TypeName]
      ,[UrChgTotal]
      ,[ZeroDateTime]
      ,[RowUpdateDateTime]
      ,[FinalDateTime]
      ,[LastBilledDateTime]
      ,[LateChargeTotal]
      ,[ProfessionalChargeTotal]
  FROM [livedb].[dbo].[BarPeStatusVectors]
  where PeriodDateTime='11/30/13' --DATEADD(m,-1,getdate())
 
  and BarStatus in ('UB','FB','IB') and Balance <>0




I commented out your suggestion above.

Current syntax will produce data.

Glen
0
 

Author Closing Comment

by:GPSPOW
ID: 39732314
Thank you that worked perfecty.

Glen
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39732316
i see whats happening that returns date and time, to use it in a query you need time to be 00:00:00

this will do that for you

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question