Is there a way to subtract 1 day from the date using SQL in this scenario?

Is there a way to subtract 1 day from the date using SQL in this scenario?

FirstMonth = 2018-01-31
SecondMonth = 2018-02-02

conn.open "select * FROM [ExportSales].[dbo].[Transaction_report_3-8-2018v2] WHERE Category = 'Sales' AND cast(Date as Date) > ('" & (FirstMonth) & "' ) AND cast(Date as Date) < '" & (SecondMonth) & "' ORDER BY Date ASC", "DSN=sales"

Open in new window

LVL 1
bschwartingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
use
DATEADD(DAY, -1, GETDATE())

Open in new window

Replace GETDATE() with whatever other date value you want to use.
0
_agx_Commented:
FirstMonth = 2018-01-31
SecondMonth = 2018-02-02

From which date? FirstMonth, SecondMonth, both?

Can those values be *more* than 2 days apart?

What's the expected result?  "All records dated 2018-02-02 , regardless of time"?
0
bschwartingAuthor Commented:
How do I implement that into my connection string?
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

bschwartingAuthor Commented:
agx, yes, those dates can be anything.  from FirstMonth to start.
0
_agx_Commented:
So which value are you trying to subtract 1 day from  FirstMonth or SecondMonth?

Russ's answer shows how to subtract a day from any date.  
https://www.experts-exchange.com/members/Russ_Suter.html
0
Russ SuterCommented:
It doesn't go in your connection string. It goes into your SQL query syntax. In the case of your example query above, I'm guessing you want it where your SecondMonth value currently is but that's just a guess.
0
bschwartingAuthor Commented:
Expected results:
My code looks at FirstMonth > and SecondMonth <

Right now though, it's a little confusing because on my HTML date form, I have to select the date before. So if I want to search all of February data, I have to select January 31st and March 1st. I want to be able to select the actual days and then just subtract one day on the front end, and add one day on the back end for the user.
0
Russ SuterCommented:
Here's a simple code snippet that demonstrates how to get the first and last day of a month given any single date value
DECLARE @EnteredDate DATE
SET @EnteredDate = '3/12/2018'

DECLARE @StartDate DATE
DECLARE @EndDate DATE

SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @EnteredDate), 0)
SET @EndDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartDate))

PRINT @StartDate
PRINT @EndDate

Open in new window

0
bschwartingAuthor Commented:
Back to my original question, how do I implement that into this?

conn.open "select * FROM [ExportSales].[dbo].[Transaction_report_3-8-2018v2] WHERE Category = 'Sales' AND cast(Date as Date) > ('" & (FirstMonth) & "' ) AND cast(Date as Date) < '" & (SecondMonth) & "' ORDER BY Date ASC", "DSN=sales"
0
Russ SuterCommented:
First, change your functionality so that it accepts only one date value. In your example we'll call that value "UserMonth" and we'll use it to replace "FirstMonth" and "SecondMonth". Then your query would look like this:
select * FROM [ExportSales].[dbo].[Transaction_report_3-8-2018v2] WHERE Category = 'Sales' AND cast(Date as Date) >= (DATEADD(MONTH, DATEDIFF(MONTH, 0, '" & UserMonth & "'), 0)) AND cast(Date as Date) <= DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, '" & UserMonth & "'), 0))) ORDER BY Date ASC", "DSN=sales

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bschwartingAuthor Commented:
Why are we going to one date value?  I have two input boxes, so my user can select the date range.
0
Russ SuterCommented:
OK, so if you only want data from February, what does your input look like?
0
bschwartingAuthor Commented:
Ok, I'm embarrassed to say that it finally triggered what I was doing wrong when I looked at your last set of code.  I changed my  > to >= and < to <= and it's picking up the 1st and last date correctly now.  Sorry all!
0
Russ SuterCommented:
No worries, things like that catch us all out from time to time.
0
_agx_Commented:
cast(Date as Date)
@bschwarting - Sorry had to put out a fire.   Just a suggestion, but .. it's generally better to avoid using functions on a column when possible. Otherwise, the database may be unable to use any indexes on that column.  Assuming FirstMonth = 2018-01-01 and
SecondMonth = 2018-01-31, this would return all records dated in January 2018

WHERE  Date >= CAST(RangeStartDate AS Date)
AND       Date < DATEADD(DAY, 1, RangeEndDate)
2
PortletPaulEE Topic AdvisorCommented:
Please don't use cast(Date as Date) it isn't necessary (and as stated by  _agx_ it is far better to avoid functions on data in the where clause).

Let's assume your inputs
FirstMonth = 2018-02-01
SecondMonth = 2018-03-01

Even if the column called [Date] is actually a datetime or datetime2 or smalldate column then any time of day portion of that data is taken into account because of the >= or <

e.g.
[ID], [Date]
1, 2018-02-01 00:00:00
2, 2018-02-01 00:00:01

BOTH of those rows are already greater than, or equal, to 2018-02-01 without using any functions on them, so that is no need to cast([date] as date)
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.