SQL on dates

[strRusty_gal]

Hi Everyone,

Thank you for the time you spend to look at the post.

I am required to retrieve report based on a date field in the table. the date field filtering must be from the previous month of the 6th till this month of the 5th.

For example, today is 5th November 2013, I need report from 6th October to 5th November.

Can someone give me some hint how doing it? I tried DATEDIFF and DATEADD, but just couldn't figure out how it should be. I hope someone will assist me by just dropping me hints.

Please let me know if explaination needed.

[pritaeas]

Have you tried with DAY(), MONTH() and YEAR() ?

What server are you using?

[strRusty_gal]

Hi pritaeas,
I am using MSSQL. Are you saying to hardcode the day to 6 and the month to previous month and year to get the current year?

But i need to get from 5th October to 6th november, how do i filtering on date range by using day , month and year?

Thank you!

[pritaeas]

Are you saying to hardcode the day

No. You can use MONTH(GETDATE()) - 1 and DAY(GETDATE()) + 1, for example, and combine it with DATEFROMPARTS()

SELECT * FROM table WHERE datefield BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL 1 DAY) AND CURDATE()

Should do it if you're not at the end of the month (29/30/31). Not sure how this works with overflows. Probably better to set the date parameters in php and include them in your SQL .

How do you want to deal with dates, e.g. 2013-10-31 - From your logic, we'd be looking at 2013-09-32 - which would = 2013-10-02

//EDIT
Disregard - was in MySQL mode - I'll leave it here though

[pritaeas]

@diafol: That looks like MySQL... and no, I didn't downvote it.

@diafol: That looks like MySQL... and no, I didn't downvote it

Yep, you're right - was in MySQL mode - my eyes!! Heh. Disregard my post. I noticed, your post was downvoted for some strange reason too, so I upped it ;)

[strRusty_gal]

I am thinking.. If i do MONTH(GETDATE())-1 then when it comes to JANUARY then the month will be 0.

maybe i can try case when? @pritaeas, thank you for your advice. Let me try for a day and will get back soon if i have any result.

[strRusty_gal]

Hi pritaeas,

Correct me if i am wrong. DATEFROMPARTS() function is only suitable for MSSQL 2012 right? I am using older version of MSSQL.

[pritaeas]

maybe i can try case when?

Indeed, you need it for the edge cases.

DATEFROMPARTS() function is only suitable for MSSQL 2012 right?

Right, sorry. Didn't notice. You can get it to work with DATE_ADD() too.

[M.Waqas Aslam]

May be this one help you
select * from tbl where myDate between dateadd(day,1,dateadd(month,-1, myDate)) and myDate

Regards

[pritaeas]

So now the next issue, how does the above cope with a date like 2013-03-31 ?

[strRusty_gal]

Hi guys,

Thank you for all your response. I have made it by hardcoding the values as the requirement from the user is fix.
with @pritaeas guidance above, i managed to came out the solution below.

This is my solution, might not be great solution but short and quick solution.

declare @monthfrom varchar(10)
declare @yearfrom varchar(10)
set @monthfrom = month(getdate()) - 1
set @yearfrom = year(getdate())
set @yearfrom = CASE @monthfrom WHEN '0' THEN year(getdate()) - 1 ELSE @yearfrom END
set @monthfrom = CASE @monthfrom WHEN '0' THEN '12' ELSE @monthfrom END
declare @combinefrom varchar(10)

select @combinefrom = '06' + '/' + @monthfrom + '/' + @yearfrom