Get first and last day of previous month (with timestamp) in SQL Server

 First Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

Last Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

First Day Of Last week.

select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

Last Day Of Last Week.

select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

First Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

Last Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

First Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

Last Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

In this Example Works on Only date is 31. and remaining days are not.

First Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

Last Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

First Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

Last Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

First Day Of Current Year.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

Last Day Of Current Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

Row wise data view as Colomn wise using PIVOT Column

drop table  AUTOREP_SIZE_RATIO

CREATE TABLE [dbo].AUTOREP_SIZE_RATIO(

[RATIO_ID] [bigint] NOT NULL,

[RATIO_NO] [int] NOT NULL,

[CATID] [int] NULL,

[SUB_ID] [int] NULL,

[SIZE_ID] [int] NULL,

[QTY] [int] NULL

) ON [PRIMARY]

GO

insert into  AUTOREP_SIZE_RATIO(

RATIO_ID, RATIO_NO, CATID, SUB_ID, SIZE_ID, QTY)values

(1000,1,100,101,1043,1),

(1000,1,100,101,1044,1),

(1001,2,100,101,1042,2),

(1001,2,100,101,1043,2),

(1001,2,100,101,1044,2),

(1002,3,100,101,1042,3),

(1002,3,100,101,1043,3),

(1002,3,100,101,1044,3),

(1002,3,100,101,1045,3)

DECLARE  @CATID nvarchar(max)=100,

DECLARE  @SUB_ID nvarchar(max)=101

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),

@PivotColumnNames AS NVARCHAR(MAX),

@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column

SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')

+ QUOTENAME(size)

FROM (SELECT DISTINCT AM.[DESCRIPTION] size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat

--select @PivotColumnNames

SELECT @PivotSelectColumnNames 

= ISNULL(@PivotSelectColumnNames + ',','')

+ 'ISNULL(' + QUOTENAME(size) + ', 0) AS '

+ QUOTENAME(size) 

FROM (SELECT DISTINCT AM.[DESCRIPTION] size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat order by size

--select @PivotSelectColumnNames

SET @DynamicPivotQuery = 

N'SELECT RATIO_ID,RATIO_NO,

' + @PivotSelectColumnNames +' from

(SELECT RATIO_ID,RATIO_NO,qty,DESCRIPTION size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= '+@CATID+' and SR. SUB_ID= '+@SUB_ID+')D 

pivot(sum(QTY) for size in (' + @PivotColumnNames + ')) as pvt' ;

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

 

Create DEMO DATE

 

declare @StartDT SMALLDATETIME='2017-05-01', 

@EndDT SMALLDATETIME='2017-05-30',

 @BUNIT_ID INT=10084

-- '2021-05-01','2021-05-31', 10084

;WITH DateRange(DateData) AS 

(

SELECT @StartDT as Date

UNION ALL

SELECT DATEADD(d,1,DateData)

FROM DateRange 

WHERE DateData < @EndDT

)

    SELECT [DateData],@BUNIT_ID [BunitID] INTO #DATEX

    FROM DateRange

    OPTION (MAXRECURSION 0);