Help Me Improve my DateTime Query

SusieBright · December 14th, 2005, 07:16 PM

I have been using SQL Server 2000 for about two weeks now, and I just made my first complex query. I want to know a) things I am doing wrong b) things that can be done better, and c) how you would approach this problem. I read the other thread, but I am not
at all familiar with functions or procedures.

I am creating a query to return data based on the season associated with that row. Each row has a datetime associated with it.

I came up with the following query to return all the entries for Fall:

Select RelevantInfo from Site3 where
( (convert(char(2), start_time, 110) > 9) AND
(convert(char(2), start_time, 110) < 12) ) OR // month between sept and dec
( (convert(char(2), start_time, 110) = 9) AND
(convert(char(2), start_time, 105) > 22) ) OR // or in sept after 22nd
( (convert(char(2), start_time, 110) = 12) AND
(convert(char(2), start_time, 105) < 21) ) // or in dec before 21st

mmcdonal · December 16th, 2005, 08:33 AM

Couldn't you do something like:

SELECT RelevantInfo
FROM Site3
WHERE [start_time] BETWEEN 09/22/2005 00:00:00:00 AND 12/23/2005 00:00:00:00

I don't have my reference in front of me, but that should work. Of course that only gives you this year. Perhaps 09/22/200_ or 09/22/____ etc?

HTH

mmcdonal

rgerald · December 16th, 2005, 05:10 PM

Create the following function and then call it whenever you need to know the season of a datetime:

Code:

CREATE FUNCTION dbo.ufn_Season (@RefDate DateTime)
    RETURNS Varchar(10)
AS
BEGIN
DECLARE @MonthNumber integer, @SeasonNumber integer, @SeasonName varchar(10)
SET @MonthNumber = MONTH(@RefDate)
IF @MonthNumber BETWEEN 1 AND 3
    SET @SeasonNumber = 0
IF @MonthNumber BETWEEN 4 AND 6
    SET @SeasonNumber = 1
IF @MonthNumber BETWEEN 7 AND 9
    SET @SeasonNumber = 2
IF @MonthNumber BETWEEN 10 AND 12
    SET @SeasonNumber = 3
IF (@MonthNumber IN(3, 6, 9, 12) AND DAY(@RefDate) > 20)
    SET @SeasonNumber = @SeasonNumber + 1
IF @SeasonNumber = 4
    SET @SeasonNumber = 0
SELECT @SeasonName = CASE(@SeasonNumber)
    WHEN 0 THEN 'Winter'
    WHEN 1 THEN 'Spring'
    WHEN 2 THEN 'Summer'
    WHEN 3 THEN 'Autumn'
END
RETURN @SeasonName
END
GO

Rand

nalla · December 21st, 2005, 01:39 AM

Hi,
when we CONVERT a date time field it'll do a table scan instead of an index seek/scan and it'll degrade performance. So instead of using CONVERT function you can use DATEPART function as,

Select RelevantInfo from Site3 where
( (datepart(mm,start_time) > 9) AND
(datepart(mm,start_time) < 12) ) OR // month between sept and dec
( (datepart(mm, start_time) = 9) AND
(datepart(dd,start_time) > 22) ) OR // or in sept after 22nd
( (datepart(mm, start_time) = 12) AND
(datepart(dd, start_time) < 21) ) // or in dec before 21st

nalla