• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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