Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I query a date range from beginning to end of the previous month in YYYYMMDD format?

Posted on 2011-03-17
5
Medium Priority
?
672 Views
Last Modified: 2012-05-11
I currently have a large amount of SQL queries built for the purpose of Month End reporting.  A common factor in these queries is retrieving total counts in tables between the first and last days of the previous month.  The field in question is BookDate, which is formatted as YYYYMMDD.  

Example:
Say today is March 1st, 2011 (20110301) and I want a total count of transactions in the ActivityHist table that are cash from February 1st, 2011 (20110201) through February 28th (20110228)... (See Attached Code)

Currently, I manually change all BookDate info to reflect the previous month... How can I do this automatically?

Regards,

NCollinsBBP


SELECT COUNT(*)
FROM ActivityHist
WHERE CashTran = '1' AND
      BookDate >= '20110201' AND BookDate <= '20110228'

Open in new window

0
Comment
Question by:NCollinsBBP
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 35158524
To get the first and last day of the previous month,

SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LastDayPrevMonth,
      CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),CAST(GETDATE() as date)),101) AS FirstDayPrevMonth
0
 
LVL 18

Expert Comment

by:lludden
ID: 35158570
If you need this formatted as a string, then use CONVERT to change to YYYYMMDD format,

SELECT CAST(varchar(8),getdate(),112)

0
 

Author Comment

by:NCollinsBBP
ID: 35159056
If you don't mind, how exactly can I incorporate that into the query I attached?  The BookDate field in my database is plain text and not a datetime field....
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 1000 total points
ID: 35159420
Try:

SELECT COUNT(*)
FROM ActivityHist
WHERE CashTran = '1' AND
      left(BookDate,6) = left(convert(varchar(8),dateadd(mm,-1,getdate()),112),6)

Open in new window


I hope this helps.
0
 

Author Closing Comment

by:NCollinsBBP
ID: 35159503
Perfection!
This solution gave me the same exact result as my attached query from my original post.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

577 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