CODEWITHSUNDEEP
sqlsql-serverexcel
Surendra Mourya
Surendra Mourya

Reputation: 611

Between clause for sql query(SQL server) not working?

I have a problem where i have a simple sql query as displayed below:

Select 
    Ah_editime as todaysdate,
    (CONVERT(VARCHAR(25), DATEADD(dd, -(DAY(GETDATE()) - 1), GETDATE()), 103)) AS monthstartdate,
    (CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE), 103)) AS monthcurrentdate
from 
    Transaction
where 
    Ah_editime BETWEEN (CONVERT(VARCHAR(25), DATEADD(dd, -(DAY(GETDATE()) - 1), GETDATE()), 103)) 
               AND (CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE), 103))

I want to display result only for current month to till date. But the problem that I face is I get the values from past month as well which creates issues. I have a report that displays the values.

report

Here if you see I am getting all the values but I want for the current month only.

P.S : Can that be the format issue? todaysdate that is getting displayed which is my actual value.

Upvotes: 3

Views: 517

Answers (2)

Rao Y
Rao Y

Reputation: 99

Date format should be modified as below in your query. Instead of 103 use 101. 

BETWEEN (CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND (CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE),101))

Upvotes: 2

Mostafa Armandi
Mostafa Armandi

Reputation: 939

Based on this post, what about following query :

DECLARE @monthStartDate AS DATE 
DECLARE @monthCurrentDate AS DATE
SELECT  @monthStartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SELECT  @monthCurrentDate = GETDATE()

SELECT  CAST(Ah_editime AS DATE) AS todaysdate ,
        @monthStartDate AS MonthStartDate ,
        @monthCurrentDate AS MonthCurrentDate
FROM    [Transaction]
WHERE   Ah_editime BETWEEN @monthStartDate AND     @monthCurrentDate

Upvotes: 0

Related Questions