SQL Where Date ....

[sculptorlv]

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!

[vikasmahajan]

datepart will be your date field !!!!

[sculptorlv]

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.

[vikasmahajan]

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

Thanks

Vikas

[tamilarasu]

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

[sculptorlv]

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.

[sculptorlv · Accepted solution]

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

[flipside]

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)

[sculptorlv]

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

[flipside]

Probably the same Mr Google I know !!

[#11]

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.