Solved: Re: SQL Where Date .... - Qlik Community

sculptorlv · ‎2016-01-21

Hello again.

I have a lot of records with dates.

How can I in SQL make a selection in order to get records only with the date of previous month?

For example, now GetDate() = 21.01.2016 ...

So I need all records from December of year 2015.

How to make necessary WHERE selection?

WHERE Record_date.... ???

P.S.: I use MS SQL

Thank you in advance for helping!

sculptorlv · ‎2016-01-21

I guess, I font the solution:

WHERE

Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

AND

Date <= DATEADD(DAY, -(DAY(GETDATE())), GETDATE());

I got information from this link:

How to get First and Last day of a month – TSQL | Sql And Me

View solution in original post

timsaddler · ‎2016-01-21

Try this :-

select * from tablename

where datepart(mm,Date) = datepart(mm,(dateadd(mm,-1,getdate())))

and datepart(yy,Date) = datepart(yy,(dateadd(yy,-1,getdate())))

sculptorlv · ‎2016-01-21

This would not work correctly.

I also can define Date > 31.11.2015 and < 01.01.2016

Your solution will not for after one month.

vikasmahajan · ‎2016-01-21

select * from tablename

where between ( datepart(mm,Date) , datepart_from(mm,Date) , datepart_to(mm,Date))

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

sculptorlv · ‎2016-01-21

I forgot to mention ... I use Microsoft SQL

Will this function BETWEEN work in it?

Report Inappropriate Content · ‎2016-01-21

Hi Ruslans,

Which database are you working on?

Thanks,

Sreeman

vikasmahajan · ‎2016-01-21

yes

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

vikasmahajan · ‎2016-01-21

like this

WHERE [INVDET_DOCDATE] BETWEEN [PB_EFF_FROM] AND [PB_EFF_TO]

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

sculptorlv · ‎2016-01-21

I got some syntax error

vikasmahajan · ‎2016-01-21

select * from tablename

where datepart(mm,Date) between datepart_from(mm,Date) and datepart_to(mm,Date)

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.