Select All Dates Between First Day of Month and Current Date

Select all dates between first day of month and current date

Try this

DECLARE @startDate DATE=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR)  -- mm/dd/yyyy
DECLARE @endDate DATE=GETDATE() -- mm/dd/yyyy

SELECT [Date] = DATEADD(Day,Number,@startDate)
FROM master..spt_values
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate

OR

DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= GETDATE() -- mm/dd/yyyy

;WITH Calender AS
(
SELECT @startDate AS CalanderDate
UNION ALL
SELECT CalanderDate + 1 FROM Calender
WHERE CalanderDate + 1 <= @endDate
)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25)
FROM Calender
OPTION (MAXRECURSION 0)

enter image description here

Get all dates between two dates in SQL Server

My first suggestion would be use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

DECLARE @MinDate DATE = '20140101',
@MaxDate DATE = '20140106';

SELECT Date
FROM dbo.Calendar
WHERE Date >= @MinDate
AND Date < @MaxDate;

If you don't want to, or can't create a calendar table you can still do this on the fly without a recursive CTE:

DECLARE @MinDate DATE = '20140101',
@MaxDate DATE = '20140106';

SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

For further reading on this see:

  • Generate a set or sequence without loops – part 1
  • Generate a set or sequence without loops – part 2
  • Generate a set or sequence without loops – part 3

With regard to then using this sequence of dates in a cursor, I would really recommend you find another way. There is usually a set based alternative that will perform much better.

So with your data:

  date   | it_cd | qty 
24-04-14 | i-1 | 10
26-04-14 | i-1 | 20

To get the quantity on 28-04-2014 (which I gather is your requirement), you don't actually need any of the above, you can simply use:

SELECT  TOP 1 date, it_cd, qty 
FROM T
WHERE it_cd = 'i-1'
AND Date <= '20140428'
ORDER BY Date DESC;

If you don't want it for a particular item:

SELECT  date, it_cd, qty 
FROM ( SELECT date,
it_cd,
qty,
RowNumber = ROW_NUMBER() OVER(PARTITION BY ic_id
ORDER BY date DESC)
FROM T
WHERE Date <= '20140428'
) T
WHERE RowNumber = 1;

How do I select between the 1st day of the current month and current day in MySQL?

select * from table_name 
where (date between DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day) AND CURDATE() )

Or better :

select * from table_name 
where (date between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )

SQL SELECT First Day and Last Day of the Month.

Convert @FirstDOM to DATE as below:

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())-1),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))
SELECT @FirstDOM,@LastDOM

I hope this will help!

Thanks,

Swapnil

How can I select the first day of a month in SQL?

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

Get All Dates of Given Month and Year in SQL Server

You can't get all days just by declaring the month, you need to add the year as well because of leap years:

DECLARE @date DATE = getdate()

;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top(day(EOMONTH(@date)))
N day,
dateadd(d,N-1, @date) date
FROM tally

Another different solution(by t@clausen):

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016

;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT N day,datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))


Related Topics



Leave a reply



Submit