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?
 
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
bschwartingAuthor Commented:
How do I implement that into my connection string?
0
 
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
 
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.