Date functions in Stored Procedure

Question

Overview : I want to show the weekly result by input parameters startdate and enddate. I am getting this result quite well. But the problem here is, When i want start date from 28/08/2015 from end date 04/09/2015 am getting 28, 29, 30, 31, 01, 02, 03, 04 from same month(august). Expected result should be 28, 29, 30, 31 from august and 01, 02, 03, 04 from september.

Help me to overcome this problem. Below is my code

ALTER PROCEDURE [dbo].[usp_Get_TimesheetDetails]
    @UserID int, @startdate datetime, @enddate datetime
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @intStartDate int
declare @intEndDate int, @diff int
declare @strMonth varchar(50)

    Select @intStartDate =   DATEPART(day, @startDate) 

    Select @intEndDate = DATEPART(day, @endDate)

    select @strMonth =  DATENAME(MONTH, GETDATE())

Declare @temptable table (num date )

Declare @columns varchar(max)
DECLARE @sqlText nvarchar(1000); 
DECLARE @startnum INT = @intStartDate-1
DECLARE @endnum INT = @intEndDate


select @diff =  DATEDIFF(MONTH, @startdate, @enddate)



     ;WITH gen AS (
         SELECT @startdate AS num
         UNION ALL
         SELECT DATEADD(DAY,1,num) FROM gen 
         WHERE DATEADD(DAY,1,num) <= @enddate
         )
      insert into @temptable SELECT  num  FROM gen
      option (maxrecursion 10000)
      set @columns=
      (SELECT distinct  
              STUFF((SELECT ',' + CAST( DATEPART(DAY, num) as varchar(100)) [text()]
              FROM @temptable 

              FOR XML PATH(''), TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
              FROM @temptable t)

      if(@startnum < 10)
      BEGIN 

      SET @sqlText = N'SELECT ' + STUFF(REPLACE(@columns,',','],['),1,3,'') + ']' + ' FROM dbo.timesheet where month ='''+ @strMonth+''' and [Task ID] in(select TaskID from ManageTasks where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')'
      print @sqlText
      END
      else if(@startnum >= 10)
      BEGIN
      SET @sqlText = N'SELECT ' + STUFF(REPLACE(@columns,',','],['),1,4,'') + ']' + ' FROM dbo.timesheet where month ='''+ @strMonth+''' and [Task ID] in(select TaskID from ManageTasks where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')'
      END
      print @sqlText
      Exec (@sqlText)
      end
end

Edited : I tried with if else condition like, if(monthdifference is equal to 0) else(monthdifference is greater than 0). But not getting expected result.

Answer 1

try this

Declare
@StartDate datetime='2015/08/28',
@EndDate datetime='2015/09/04'
;WITH sample AS (
  SELECT CAST(@StartDate AS DATETIME) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= CAST(@EndDate AS DATETIME))
SELECT * 
  FROM sample

output is :

2015-08-28 00:00:00.000  
2015-08-29 00:00:00.000  
2015-08-30 00:00:00.000   
2015-08-31 00:00:00.000   
2015-09-01 00:00:00.000   
2015-09-02 00:00:00.000   
2015-09-03 00:00:00.000   
2015-09-04 00:00:00.000  

Original Link : https://stackoverflow.com/a/3946151/3465753

Answer 2

Make sure you declared startdate as well as enddate in dynamic query

Answer 3

The main Ideas are:

1. You don't need to use STUFF. Just select dates from DATEADD(DAY,1,@startdate)
2. You should get dates twise if DATENAME(MONTH, @startdate)!=DATENAME(MONTH, @enddate). First time from startdate to end of months. Second time from start of second month to enddate.

My (checked) script.

ALTER PROCEDURE [dbo].[usp_Get_TimesheetDetails]
    @UserID int, @startdate datetime, @enddate datetime
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

Declare @columns varchar(max);
DECLARE @sqlText nvarchar(1000); 
Declare @temptable table (num date );

     WITH gen AS (
         SELECT DATEADD(DAY,1,@startdate) AS num
         UNION ALL
         SELECT DATEADD(DAY,1,num) FROM gen 
         WHERE DATEADD(DAY,1,num) <= @enddate
      and DATEADD(DAY,1,num) < dateadd(month,datediff(month,0,@enddate),0) 
         )
      insert into @temptable SELECT  num  FROM gen
      option (maxrecursion 10000)

      set @columns=
      (SELECT distinct  
              STUFF((SELECT ',' + CAST( DATEPART(DAY, num) as varchar(100)) [text()]
              FROM @temptable

              FOR XML PATH(''), TYPE)
              .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
              FROM @temptable t)


      SET @sqlText = N'SELECT [' + REPLACE(@columns,',','],[') + ']' + ' FROM dbo.timesheet where month ='''+ DATENAME(MONTH, @startdate)+''' and [Task ID] in(select TaskID from ManageTasks where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')';
      print @sqlText;

    IF  DATENAME(MONTH, @startdate)!=DATENAME(MONTH, @enddate)  
    BEGIN
    delete from @temptable;

     WITH gen AS (
         SELECT dateadd(month,datediff(month,0,@enddate),0)  AS num
         UNION ALL
         SELECT DATEADD(DAY,1,num) FROM gen 
         WHERE DATEADD(DAY,1,num) <= @enddate
         )
     insert into @temptable SELECT  num  FROM gen
      option (maxrecursion 10000)

      set @columns=
      (SELECT distinct  
              STUFF((SELECT ',' + CAST( DATEPART(DAY, num) as varchar(100)) [text()]
              FROM @temptable

              FOR XML PATH(''), TYPE)
              .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
              FROM @temptable t)


      SET @sqlText = N'SELECT [' + REPLACE(@columns,',','],[') + ']' + ' FROM dbo.timesheet where month ='''+ DATENAME(MONTH, @enddate)+''' and [Task ID] in(select TaskID from ManageTasks where TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +')';
      print @sqlText 
     end
   end

Answer 4

You might find pivot/unpivot to be more robust. Certainly your table design is not ideal. (I pared down the number of columns for demonstration purposes.)

create table dbo.timesheet (
    [month] varchar(12) not null,
    [1] int null, [2] int null, [3] int null,
    [28] int null, [29] int null, [30] int null, [31] int null
);

declare @startDate date = '20160628';
declare @endDate date = '20160703';

insert into dbo.timesheet ([month], [1], [2], [3], [28], [29], [30], [31])
values ('June', 1, 2, 3, 4, 5, 6, null), ('July', 8, 9, 10, 11, 12, 13, 14);


with hrs as (
select
    hrs,
    dy,
    dateadd(
        month,
        case [month]
            when 'January' then 1 when 'February' then 2 when 'March' then 3
            when 'April' then 4 when 'May' then 5 when 'June' then 6
            when 'July' then 7 when 'August' then 8 when 'September' then 9 
            when 'October' then 10 when 'November' then 11 when 'December' then 12
        end,
        dateadd(year, year(getdate()) - 2000, dateadd(day, dy - 1, '19991201'))
    ) as dt
from
    (select [month], [1], [2], [3], [28], [29], [30], [31] from dbo.timesheet) t
    unpivot (hrs for dy in ([1], [2], [3], [28], [29], [30], [31])) as upvt
)
select datename(month, dt), [1], [2], [3], [28], [29], [30], [31]
from hrs pivot (min(hrs) for dy in ([1], [2], [3], [28], [29], [30], [31])) as pvt
where dt between @startDate and @endDate;