• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

datepart problem

I'm trying to produce some management reports wherethe user will decide if they want a week todate or day todate report.  Is it possible to do something like the below statement.

create p1
(@part nvarchar(10))
as

select somedata
from sometable
where datepart(@part,createdon) =  datepart(@part,getdate())
0
leap29
Asked:
leap29
1 Solution
 
brad2575Commented:
no but you could build a string with the SQL in it and execute the string like this:

Set @sql = 'select somedata from sometable where datepart(' + @part + ',createdon) =  datepart(' + @part + ',getdate()) '

EXEC(@sql)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, the part cannot be a parameter.

instead, use this:
create p1
(@part nvarchar(10))
as
declare @startdate datetime
declare @enddate datetime 
set nocount on
select @startdate = case @part 
   when 'day' then convert(datetime, convert(varchar(10), getdate(), 120), 120)
   when 'month' then convert(datetime, convert(varchar(8), getdate(), 120) + '01' , 120)
   when 'week' then dateadd(day, 1-datepart(weekday, getdate(), convert(datetime, convert(varchar(10), getdate(), 120), 120))
    end
     , @enddate = case @part
   when 'day' then dateadd(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
   when 'month' then dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
   when 'week' then dateadd(day, 8-datepart(weekday, getdate(), convert(datetime, convert(varchar(10), getdate(), 120), 120))
    end
select somedata
from sometable
where createdon >= @startdate 
  and createdon <  @enddate

Open in new window

0
 
leap29Author Commented:
thanks for the reply.  I've tried the solutin above and it work great fro the month and day, how ever the code errors with the week option.  
when 'week' then dateadd(day, 1-datepart(weekday, getdate(), convert(datetime, convert(varchar(10), getdate(), 120), 120))
   

The datepart function requires 2 argument(s).  
0
 
Kevin CrossChief Technology OfficerCommented:
I think you are just missing a paranthesis:
alter procedure p1
(@part nvarchar(10))
as
declare @startdate datetime
declare @enddate datetime 
set nocount on
select @startdate = case @part 
   when 'day' then convert(datetime, convert(varchar(10), getdate(), 120), 120)
   when 'month' then convert(datetime, convert(varchar(8), getdate(), 120) + '01' , 120)
   when 'week' then dateadd(day, 1-datepart(weekday, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120))
    end
     , @enddate = case @part
   when 'day' then dateadd(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
   when 'month' then dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
   when 'week' then dateadd(day, 8-datepart(weekday, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120))
    end
select somedata
from sometable
where createdon >= @startdate 
  and createdon <  @enddate

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Glad that helped.

Angel Eyes gave original solution though, so you can always request the points be spread differently and his solution be accepted also.

Kevin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now