Get all the date of current year or particular year, below is the scrip to get the record from sql server.
- Declare @Year AS INT = DATENAME(
- YYYY,
- getdate()
- );
- declare @FromDate datetime = DATEADD(yyyy, @Year -1900, 0),
- @ToDate datetime = DATEADD(yyyy, @Year -1900 + 1, -1);
- WITH Dates (DateNo) AS (
- SELECT
- DATEADD(
- DAY,
- DATEDIFF(DAY, 0, @ToDate) - DATEDIFF(DAY, @FromDate, @ToDate),
- 0
- )
- UNION ALL
- SELECT
- DATEADD(DAY, 1, DateNo)
- FROM
- Dates
- WHERE
- DATEADD(DAY, 1, DateNo) <= @ToDate
- )
- SELECT
- DateNo as [Date],
- DATENAME(month, DateNo) as [Month Name],
- DATENAME(DW, DateNo) as [Day Name]
- FROM
- Dates
- option (maxrecursion 365);