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)
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
Count All Records Per Day in a Specific Month
Select to Get Rows Based on Minimum Value of a Column
Laravel Update Multiple Records At Once
Find All Parent Records Where All Child Records Have a Given Value (But Not Just Some Child Records)
Reactjs Connection With Database
Concatenate With String But Exclude When Null
Remove Blank Line in Between Select Queries When Spooling to CSV File
How to Set Timezone for Postgres Psql
Calculate Total Working Hour of Employee in SQL With Only 1 Column
Difference Between "INNER JOIN" and "OUTER JOIN"
Oracle Sql: Extracting Text Between Two Characters
Mysql: Alter Table If Column Not Exists
How to Select True/False Based on Column Value
Fill a Field With the Values from Another Field in Ms Access
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes
Postgresql - Combining Multiple Rows With Several Attributes into One Row