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

Question

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.

It seems your result looks correct to me...the dates are in DDMMYYYY format, which is 1st February 2015 to 10th February 2015. — Rao Y, Feb 10 '15 at 05:49
or else use the following date format in your query to see date in MMDDYYYY. `BETWEEN (CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND (CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE),101))` — Rao Y, Feb 10 '15 at 05:58
@RaoY: this is not what i am expecting. the result(todaysdate) i need should be between current month start date i.e 1 feb and current date 10feb. But this is not working. — Surendra Mourya, Feb 10 '15 at 06:04
@RaoY: Thanks Rao, it worked. If you post this as answer i will accecpt it. — Surendra Mourya, Feb 10 '15 at 06:08

Rao Y · Accepted Answer · 2015-02-10T07:35:58.517

2

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))

edited Feb 10 '15 at 07:35

answered Feb 10 '15 at 06:30

Rao Y

99
5

score 0 · Answer 2 · edited May 23 '17 at 12:05

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

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

2 Answers2