sql server programming: Assistance ith datetime and datename -- sql server programming

Re: Assistance ith datetime and datename

Chris
6/21/2007 6:17:00 PM

The issue I have is, the application (asp.net) is passing the following params

year = 2006

month from = January

month to = July

and they expect me to look this up as between 2006-01-01 and 2006-07-31. How
then should I accomplish this?

Thanks

[quoted text, click to view]

Re: Assistance ith datetime and datename

Andrew J. Kelly
6/21/2007 9:01:38 PM

Well first off you should never format a WHERE clause like that, meaning as
soon as you add functions to the column you loose the ability to do an index
seek. You should always change what you are comparing the column to so you
can do a direct compare to the values in the column.

where traxdate BETWEEN '20060101' AND '20060228'
AND product_number = 1234

Second how do you expect "datename(month,traxdate) between 'January' AND
'February'" to really work? DateName returns a varchar. How are you
expecting it to do a BETWEEN amongst all the months? Keep the values as
Datetimes and you should be fine.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: Assistance ith datetime and datename

Tom Cooper
6/21/2007 11:37:10 PM

One way:

Build a small table of month names and month numbers
Create Table Months (MonthName varchar (10) Not Null Primary Key,
MonthNumber char(2) Not Null)
Insert Months (MonthName, MonthNumber)
Select 'January', '01'
Union All Select 'February', '02'
Union All Select 'March', '03'
-- etc
Union All Select 'December', '12';

Then you can use this table to build datetimes with the start date as the
first date of the month from and the end date as the first date of the month
following the month to. Then use these dates to select from your table.
For example,

Declare @Year int,
@MonthFrom varchar(10),
@MonthTo varchar(10),
@StartDate datetime,
@EndDate datetime;
Select @Year = 2006, @MonthFrom = 'January', @MonthTo = 'December';
Select @StartDate = Cast(@Year as char(4)) + '/' + m.MonthNumber + '/01'
From Months m
Where m.MonthName = @MonthFrom;
Select @EndDate = DateAdd(mm, 1, Cast(@Year as char(4)) + '/' +
m.MonthNumber + '/01')
From Months m
Where m.MonthName = @MonthTo;
-- Now do the select from your table
SELECT product_number,
delivery_date,
....
from dbo.archive
Where trandate >= @StartDate
AND traxdate < @EndDate
AND product_number = 1234
group by
product_number,
delivery_date
order by product_number;

Tom

[quoted text, click to view]

Re: Assistance ith datetime and datename

Andrew J. Kelly
6/22/2007 1:26:27 PM

You could use a table but a simple case expression should work pretty well.
The year is pretty straight forward but for the month something like this
assuming they pass these into a stored proc:

SET @From = CAST(CAST(@Year AS CHAR(8)) + CASE @Month WHEN 'January' THEN
'01'
WHEN 'Feburary' THNE '02'
...
WHEN 'November = '11'
ELSE '12' END + '01' AS DATETIME)

SET @To = CAST(CAST(@Year AS CHAR(8)) + CASE @Month WHEN 'January' THEN '01'
WHEN 'Feburary' THNE '02'
...
WHEN 'November = '11'
ELSE '12' END + '01' AS DATETIME)

-- Add a month
SET @To = DATEADD(mm,1,@To)

-- Note the less than on the From
SELECT * FROM Table WHERE TaxDate >= @From and TaxDate < @From

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.