help with date parameters

Question

Post reply

help with date parameters

Kristin Stariha

Ten Centuries

Points: 1012
More actions
May 25, 2005 at 11:04 am

#167050

Hi all! I need some help with my date parameters. I hope I can explain this clearlyJ I have a report built like so:

            Column_a          Column_b          Column_c

Jan
Feb
Mar
Apr
May
June
July
Aug
Sep
Oct
Nov
Dec

Each value in the column will have it’s own query based on a set of date parameters. However, I would like my user to enter only the year and have it populate my table. I would like for instance the January/Column_a query to look like this: (of course coded correctlyJ)

SELECT     COUNT(*) AS Total
FROM         records
WHERE     (reportsentdate >= '1/1/' + @year) AND (reportsentdate <= '1/31/' + @year)

How do I concantenate part of the date with the year as parameter?

Does anyone have any ideas for me?

Thanks!
kristin
Kristin

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I think this will do all you want in a single query :

Declare @StartD as datetime

declare @EndD as datetime

declare @Year as int

set @Year = 2004

set @StartD = dateadd(YYYY, @Year - 1900, 0)

set @EndD = dateadd(YYYY, 1, @StartD)

Select @Startd, @EndD

--2004-01-01 00:00:00.0002005-01-01 00:00:00.000

Select count(col1) as Tot1, Count(col2) as Tot2, datename(m, reportsentdate) as MonthName --,...

from dbo.Records

where reportsentdate >= @StartD and reportsentdate < @EndD

GROUP BY Month(reportsentdate)

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 2

Maybe I should have been more specific Ignore Column_b and Column_c - as they are values derived in a completely different way. All I really need is a way to concatenate a string with the parameter. The string being the month and day and the parameter being the year. Looks like I made it sound more complicated than it is.

Kristin

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It's simple really, but the fact is that it's much simpler to run a single query than to run 12. My 2 count() were mere exemple for the aggregates that need to me there.

Have you tried adapting this query to your needs?

ron k SSCertifiable Points: 6103 More actions · Answer 4

That's what Remi gave you (and a little more).

Declare @StartD as datetime

declare @EndD as datetime

declare @Year as int

set @Year = 2004

set @StartD = dateadd(YYYY, @Year - 1900, 0)

set @EndD = dateadd(YYYY, 1, @StartD)

Select @StartD

-------------------------

2004-01-01 00:00:00.000

Select @EndD

-------------------------

2005-01-01 00:00:00.000

and please note the @EndD since that is what you really need.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Hey ron... I guess it's starting to sink in now

"where reportsentdate >= @StartD and reportsentdate < @EndD"

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 6

This is what I got to work.....in case anyone was interested

SELECT COUNT(*) AS Total

FROM records

WHERE (DATEPART(year, reportsentdate) = @year) AND (DATEPART(month, reportsentdate) = '01')

Kristin

ron k SSCertifiable Points: 6103 More actions · Answer 7

Using the DATEPART function will not allow the optimizer to use the index on the reortsentdate column so your statement will force a table scan.

For more info on how to work with sql server dates you might want to look at these articles:

http://www.sql-server-performance.com/fk_datetime.asp

http://www.karaszi.com/SQLServer/info_datetime.asp

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Yes ron k and again this will scan only 1 month at the time. Unless this is exactly what you (kristin) want it to do then I would urge you to try to understand my query as it is immensly more flexible and optimized that your stored proc.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Please run this in query analyser to see the resultset it presents :

Declare @StartD as datetime

declare @EndD as datetime

declare @Year as int

set @Year = 2004

set @StartD = dateadd(YYYY, @Year - 1900, 0)

set @EndD = dateadd(YYYY, 1, @StartD)

Select count(*) as TotalObjects, XType, min(datename(m, refdate)) as MonthName

from dbo.SysObjects

where refdate >= @StartD and refdate = @StartD and refdate < @EndD

GROUP BY Month(refdate)

ORDER BY Month(refdate)

This solution could be very easyly adapted to accomodate date ranges on multiple years

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 10

This is the result set it presented and I don't understand.... I am running a summary on activity for the year...my columns being the different activities and the months - the timeframe I'm not sure what this is....

1 D June

1 D August

6 D November

30 D December

2 P June

32 P July

1 PK June

1 PK July

2 PK August

3 PK November

3 PK December

2 U June

1 U July

4 U August

1 U September

4 U November

3 U December

Kristin

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

First column is the total count, 2nd is the type of the object (could be the activity), third is the month is was created (or month of the activity).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

If you, as I hope, pursue this solution, make sure that the date column is indexed in the activity table. The best case would be to have it be in the front of the clustered index but I'm not sure it would be the best design in this case (since I have not idea of the rest of the db).

Joe Williams-227299 SSC Journeyman Points: 87 More actions · Answer 13

Try this query:

SELECT COUNT(*) AS Total

FROM records

WHERE year(reportsentdate) = @year

Joe Williams-227299 SSC Journeyman Points: 87 More actions · Answer 14

Whoops, the last part of my suggested query was left off. This is one I would try:

SELECT COUNT(*) AS Total

FROM records

WHERE year(reportsentdate) = @year

and month(reportsentdate) = 1