topic Re: SQL Where Date .... in QlikView

SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 11:05:41 GMT

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!

Re: SQL Where Date ....

timsaddler — Thu, 21 Jan 2016 11:14:11 GMT

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

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 11:17:25 GMT

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.

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:18:21 GMT

select * from tablename

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

Vikas

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 11:21:50 GMT

I forgot to mention ... I use Microsoft SQL

Will this function BETWEEN work in it?

Re: SQL Where Date ....

Thu, 21 Jan 2016 11:26:14 GMT

Hi Ruslans,

Which database are you working on?

Thanks,

Sreeman

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:26:40 GMT

yes

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:28:05 GMT

like this

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

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 11:30:58 GMT

I got some syntax error

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:31:58 GMT

select * from tablename

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

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:33:06 GMT

datepart will be your date field !!!!

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 11:36:00 GMT

oh.. I got it. This will not help me.

I have no constant datepart_from and datepart_to .. I have only GetDate() = today date. I need to get select from previous month based on TODAY date.

Re: SQL Where Date ....

vikasmahajan — Thu, 21 Jan 2016 11:38:59 GMT

if you got solution please close thread with correct or helpful ans.

Thanks

Vikas

Re: SQL Where Date ....

tamilarasu — Thu, 21 Jan 2016 12:14:48 GMT

Hi Ruslans,

Try,

Let vStartDate = MonthStart(AddMonths(Today(),-1));
Let vEndDate = MonthEnd('$(vStartDate)');

Select * From TableName 
Where DateField > '$(vStartDate)' And DateField < '$(vEndDate)';

I assume your date format as DD/MM/YYYY. If not, Modify the vStartDate and vEndDate,

Let vStartDate = Date(MonthStart(AddMonths(Today(),-1)), 'Your DateField Format');

Let vEndDate = Date(MonthEnd('$(vStartDate)') , 'Your DateField Format');

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 14:23:46 GMT

Thanks, but this will no help me. I have to get necessary data within SQL selection.

I tried:

SQL SELECT

Amount AS Budget_Money,

Date AS Budget_data,

Year(Date) AS Budget_Year,

Month(Date) AS Budget_Month,

"Item Category Code" AS Budget_Category,

"Salesperson Code" AS Agent_Code

FROM "DB".dbo."DB$DB"

WHERE

Date > MonthEnd(DateAdd(m, GetDate(),-2))

AND

Date < MonthStart(GetDate());

But MonthEdn and MonthStart is not recognized function here.

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 14:30:32 GMT

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

Re: SQL Where Date ....

flipside — Thu, 21 Jan 2016 14:52:37 GMT

Try

SELECT *

FROM "DB".dbo."DB$DB"

WHERE Date BETWEEN

DATEADD(MONTH,DATEDIFF(month,0,dateadd(month,-1,getdate())),0)

AND

DATEADD(MONTH,DATEDIFF(month,0,dateadd(month,0,getdate())),0)

Re: SQL Where Date ....

sculptorlv — Thu, 21 Jan 2016 14:54:42 GMT

Thank you. Already got solution (very similar) form mr.google.

Re: SQL Where Date ....

flipside — Thu, 21 Jan 2016 14:59:10 GMT

Probably the same Mr Google I know !!

Re: SQL Where Date ....

Thu, 21 Jan 2016 15:00:19 GMT

Hi Ruslans,

I dont have SQL Server in my system. but the below format will help you to understand to the last month value and follow the instructions so that you can get the desired result.Please let me know if you face any issue.

Try this: DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,1-datepart(dd,getdate()),getdate()))). I will explain this one in the step by step with the bold one.

1. If the getdate() value is '2016-01-21 07:12:28' then the bold part will give you the date value like 21. Iam subtracting with one value because i will subtract this value to get the first day of Jan like '2015-12-31 07:12:28'.

DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,1-datepart(dd,getdate()),getdate())))

2. the next dateadd value will give the value as '2015-12-31 00:00:00'.

DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,1-datepart(dd,getdate()),getdate())))

By doing these steps you will get the last day of the previous month. And to get the first of the previous month. Now by doing this you will have the last day and now you can get the date

[the bold one is the last day of previous month]

dateadd(dd,1-datepart(dd,DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,1-datepart(dd,getdate()),getdate())))),DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,1-datepart(dd,getdate()),getdate()))))

Sorry for having any syntax errors in the command.

Thanks,
Sreeman.