organizing datetime by week?

[karen4201]

Hi,

I'm trying to use the code "DateAdd(day, datepart(dw, ReviewDate), ReviewDate )" to organize the ReviewDates by weeks. However, my code keeps grouping some items incorrectly. Since I don't know what is happening, I'll include all possible information.

-there is a date field that is stored by seconds since 1970, so I must do a convert
-I'm passing the query a start and end date to organize rows into weeks

question: In query results 4, why would the date selection cause some items that are less that minDate be selected? Why would "2005-02-05 11:34:35.000"
be classified as the week "2005-01-29"

here is the sql code:
===================
declare @startDate varchar(25)
declare @endDate varchar(25)
declare @ProductName varchar(250)
select @startDate = '1995/02/12'
select @endDate = '1995/02/19'
select @ProductName = 'myProduct'
declare @minDate varchar(25)
declare @maxDate varchar(25)


SELECT @minDate = convert(datetime,DateAdd(day, ( datepart(dw, @startDate)) , @startDate ),113) -- get the earliest monday in the date range
SELECT @maxDate = convert(datetime,DateAdd(day, ( datepart(dw, @endDate)) , @endDate ),113) -- get the latest monday in the date range
SELECT @minDate
SELECT @maxDate

SELECT count(DISTINCT LTRIM(RTRIM(myTable.myItem))) as [the_count],
convert(char(11),DateAdd(day,
datepart(dw, CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)),
CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)
),121) as [the_date]
FROM myTable, myOtherTable
WHERE
myOtherTable.ProductName= @ProductName
and CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121) >= @minDate
and CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121) <= @maxDate
GROUP BY convert(char(11),DateAdd(day,
datepart(dw, CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)),
CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)
),121)

select '======='


SELECT DISTINCT LTRIM(RTRIM(myTable.myItem)) as [the_count],
convert(char(11),DateAdd(day,
datepart(dw, CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)),
CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)
),121) as [the_week]

, CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121) as [the_day]
FROM myTable, myOtherTable
WHERE
myOtherTable.ProductName = @ProductName
and CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121) >= @minDate
and CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121) <= @maxDate
===================

HERE IS THE RESULT:

query 1 ruturns:
Feb 5 2005 12:00AM


query 2 returns:
Feb 12 2005 12:00AM



query 3 returns:
[the_count] [the_date]
3 2005-01-29
318 2005-02-05




query 4 returns:

myprodA 2005-01-29 2005-02-05 11:34:35.000
myprodB 2005-01-29 2005-02-05 14:16:28.000
myprodC 2005-02-05 2005-02-06 23:06:26.000
myprodD 2005-02-05 2005-02-07 08:30:44.000
myprodE 2005-02-05 2005-02-07 08:39:54.000

 

[rac2]

I dont have the answer but here are some points to consider.

The expression
DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000')
yields a DATETIME value. I think the value would be as expected, it represents in SQL Server form the same date as timeInSecs represent in myTable. timeInSecs is an INTEGER, right?

The expresssion
CONVERT(datetime, DATEADD(s, myTable.timeInSecs, '1970-01-01 00:00:00.000') , 121)
does nothing. Or rather, it converts a DATETIME value into a DATETIME value. The formatting parameter, 121, is irrelevent. It only matters when converting to VARCHAR or CHAR.

The variables @minDate and @maxDate are VARCHAR. In order to compare them to DATETIME values, either they must be converted to DATETIME values, or the DATETIME values must be converted to VARCHAR. Your query does not explicityly do either, therefore SQL Server will do one or the other. Want to bet it did the thing you did not wish?

Comparing strings, i.e., VARCHAR, gives different ordering that comparing numbers, i.e., DATETIME. This and the ambiguity mentioned above could account for the strange results.

Forgive me if I have merely stated the obvious and overlooked some of the subtle aspects of the problem.

 

[rac2]

Oh yes, DATEPART(WEEK, a_DATETIME_value) is a nice way to organize things by the week number within a year. Jan 1 is week number 1.

From Books Online
"The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.