May 5, 2011 at 3:53 am
HI All,
i want to retrieve the records from the sql server database table where the condition is one month old from the date available in the table.
There is a field called date1 which will contain the date in which the records are inserted to the table,
i dont want to change the query every time,Can any one give me a query which will perform the same.
select * from Table name where date = one month old
May 5, 2011 at 4:00 am
SELECT *
FROM YourTable
WHERE date1 >= DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 0)
AND date1 < DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 1)
May 5, 2011 at 4:08 am
Thanks its perfect
May 9, 2011 at 2:26 am
Hi ,Upon Analysis its not able to pick the record of the 31st day of the month in the month which consist of 30 days.
Example we are not able to pick the 31st march record in April as i want every month record in next month ..
Like 1st of march on 1st of april but 31st of march on 30th of april so on...
So i can't even used the 30 days substraction that will also not suits for feburary ...
So any New query..
May 9, 2011 at 3:52 am
You will have to define what you want and fix it accordingly.
eg. The following will produce 2011-01-28 to 2011-01-31 on 2011-02-28
and 2011-02-28 on 2011-03-28, 2011-03-29, 2011-03-30 and 2011-03-31.
;WITH DateRange
AS
(
SELECT
DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 0) AS DateFrom
,CASE
-- when start of next month = tomorrow
WHEN DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
-- then start of current month
THEN DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
-- else same day in last month + 1
ELSE DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 1)
END AS DateTo
)
SELECT Y.*
FROM YourTable Y
CROSS JOIN DateRange R
WHERE Y.date1 >= R.DateFrom
AND Y.date1 < R.DateTo
May 9, 2011 at 5:01 am
SELECT *
FROM YourTable
--Grabs data between the 1st of last month and the 1st of this month
WHERE date1 >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0),
AND date1 < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
May 9, 2011 at 5:21 am
thanks 🙂
May 9, 2011 at 6:58 am
Bear in mind that unlike Ken's solution, this solution will always get the data from the first of last month to the first of this month, regardless of the day that you execute it on.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply