March 9, 2009 at 2:29 pm
I am in the process of upgrading a few databases from 2000 to 2005 at the compnay I work for and came across an issue I have not seen with DATEPART before. Anyone aware of this, is it intentional or is there a patch I missed to resolve it? It only seems to happen with derived tables which worked fine under SQL 2000.
first this works fine using DATEPART
SELECT
Dates,
CASE WHEN DATEPART(dw,Dates) IN (2,3,4,5,6) THEN 1 ELSE 0 END
FROM
(
SELECT
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) Dates
FROM
(
SELECT 1 nMonth
UNION
SELECT 2 nMonth
UNION
SELECT 3 nMonth
UNION
SELECT 4 nMonth
UNION
SELECT 5 nMonth
UNION
SELECT 6 nMonth
UNION
SELECT 7 nMonth
UNION
SELECT 8 nMonth
UNION
SELECT 9 nMonth
UNION
SELECT 10 nMonth
UNION
SELECT 11 nMonth
UNION
SELECT 12 nMonth
) tMonths
cross join
(
SELECT 1 nDay
UNION
SELECT 2 nDay
UNION
SELECT 3 nDay
UNION
SELECT 4 nDay
UNION
SELECT 5 nDay
UNION
SELECT 6 nDay
UNION
SELECT 7 nDay
UNION
SELECT 8 nDay
UNION
SELECT 9 nDay
UNION
SELECT 10 nDay
UNION
SELECT 11 nDay
UNION
SELECT 12 nDay
UNION
SELECT 13 nDay
UNION
SELECT 14 nDay
UNION
SELECT 15 nDay
UNION
SELECT 16 nDay
UNION
SELECT 17 nDay
UNION
SELECT 18 nDay
UNION
SELECT 19 nDay
UNION
SELECT 20 nDay
UNION
SELECT 21 nDay
UNION
SELECT 22 nDay
UNION
SELECT 23 nDay
UNION
SELECT 24 nDay
UNION
SELECT 25 nDay
UNION
SELECT 26 nDay
UNION
SELECT 27 nDay
UNION
SELECT 28 nDay
UNION
SELECT 29 nDay
UNION
SELECT 30 nDay
UNION
SELECT 31 nDay
) tDays
cross join
(SELECT 2008 AS nYear) nYears
where
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) = 1 AND
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) BETWEEN '20080501' AND '20080506'
) tBase
ORDER BY
Dates ASC
However placing the same thing in the WHERE cluse breaks with error
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
SELECT
Dates
FROM
(
SELECT
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) Dates
FROM
(
SELECT 1 nMonth
UNION
SELECT 2 nMonth
UNION
SELECT 3 nMonth
UNION
SELECT 4 nMonth
UNION
SELECT 5 nMonth
UNION
SELECT 6 nMonth
UNION
SELECT 7 nMonth
UNION
SELECT 8 nMonth
UNION
SELECT 9 nMonth
UNION
SELECT 10 nMonth
UNION
SELECT 11 nMonth
UNION
SELECT 12 nMonth
) tMonths
cross join
(
SELECT 1 nDay
UNION
SELECT 2 nDay
UNION
SELECT 3 nDay
UNION
SELECT 4 nDay
UNION
SELECT 5 nDay
UNION
SELECT 6 nDay
UNION
SELECT 7 nDay
UNION
SELECT 8 nDay
UNION
SELECT 9 nDay
UNION
SELECT 10 nDay
UNION
SELECT 11 nDay
UNION
SELECT 12 nDay
UNION
SELECT 13 nDay
UNION
SELECT 14 nDay
UNION
SELECT 15 nDay
UNION
SELECT 16 nDay
UNION
SELECT 17 nDay
UNION
SELECT 18 nDay
UNION
SELECT 19 nDay
UNION
SELECT 20 nDay
UNION
SELECT 21 nDay
UNION
SELECT 22 nDay
UNION
SELECT 23 nDay
UNION
SELECT 24 nDay
UNION
SELECT 25 nDay
UNION
SELECT 26 nDay
UNION
SELECT 27 nDay
UNION
SELECT 28 nDay
UNION
SELECT 29 nDay
UNION
SELECT 30 nDay
UNION
SELECT 31 nDay
) tDays
cross join
(SELECT 2008 AS nYear) nYears
where
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) = 1 AND
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) BETWEEN '20080501' AND '20080506'
) tBase
WHERE
(CASE WHEN DATEPART(dw,Dates) IN (2, 3, 4, 5, 6) THEN 1 ELSE 0 END) = 1
ORDER BY
Dates ASC
I have not tried this against 2008 as of yet and there is no corporate plan to transition to 2008 yet.
March 9, 2009 at 2:35 pm
Sorry additional note. This too oddly enough fails with the same error.
select * from
(
SELECT
Dates,
(CASE WHEN DATEPART(dw,Dates) IN (2,3,4,5,6) THEN 1 ELSE 0 END) DOFW
FROM
(
SELECT
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) Dates
FROM
(
SELECT 1 nMonth
UNION
SELECT 2 nMonth
UNION
SELECT 3 nMonth
UNION
SELECT 4 nMonth
UNION
SELECT 5 nMonth
UNION
SELECT 6 nMonth
UNION
SELECT 7 nMonth
UNION
SELECT 8 nMonth
UNION
SELECT 9 nMonth
UNION
SELECT 10 nMonth
UNION
SELECT 11 nMonth
UNION
SELECT 12 nMonth
) tMonths
cross join
(
SELECT 1 nDay
UNION
SELECT 2 nDay
UNION
SELECT 3 nDay
UNION
SELECT 4 nDay
UNION
SELECT 5 nDay
UNION
SELECT 6 nDay
UNION
SELECT 7 nDay
UNION
SELECT 8 nDay
UNION
SELECT 9 nDay
UNION
SELECT 10 nDay
UNION
SELECT 11 nDay
UNION
SELECT 12 nDay
UNION
SELECT 13 nDay
UNION
SELECT 14 nDay
UNION
SELECT 15 nDay
UNION
SELECT 16 nDay
UNION
SELECT 17 nDay
UNION
SELECT 18 nDay
UNION
SELECT 19 nDay
UNION
SELECT 20 nDay
UNION
SELECT 21 nDay
UNION
SELECT 22 nDay
UNION
SELECT 23 nDay
UNION
SELECT 24 nDay
UNION
SELECT 25 nDay
UNION
SELECT 26 nDay
UNION
SELECT 27 nDay
UNION
SELECT 28 nDay
UNION
SELECT 29 nDay
UNION
SELECT 30 nDay
UNION
SELECT 31 nDay
) tDays
cross join
(SELECT 2008 AS nYear) nYears
where
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) = 1 AND
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) BETWEEN '20080501' AND '20080506'
) tBase
) X
WHERE
DOFW = 1
ORDER BY
Dates ASC
Subquery provides 1 and 0 in DOFW filed somewhere SQL 2005 just doesn't like this.
March 9, 2009 at 2:53 pm
OK found a workable solution by using a table-valued funtion to handle the dates part and substituting it out in the code. Which I can see how it works as the function returns a table variable and is not derived. But I find nothing about this DATEPART changing or how derived tables are managed. Does anyone know where this change in behavior is documented?
CREATE FUNCTION dbo.DatesInRange(@Start datetime, @Stop datetime)
RETURNS @Dates TABLE (Dates datetime)
AS
BEGIN
INSERT INTO @Dates (DATES)
SELECT
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) Dates
FROM
(
SELECT 1 nMonth
UNION
SELECT 2 nMonth
UNION
SELECT 3 nMonth
UNION
SELECT 4 nMonth
UNION
SELECT 5 nMonth
UNION
SELECT 6 nMonth
UNION
SELECT 7 nMonth
UNION
SELECT 8 nMonth
UNION
SELECT 9 nMonth
UNION
SELECT 10 nMonth
UNION
SELECT 11 nMonth
UNION
SELECT 12 nMonth
) tMonths
cross join
(
SELECT 1 nDay
UNION
SELECT 2 nDay
UNION
SELECT 3 nDay
UNION
SELECT 4 nDay
UNION
SELECT 5 nDay
UNION
SELECT 6 nDay
UNION
SELECT 7 nDay
UNION
SELECT 8 nDay
UNION
SELECT 9 nDay
UNION
SELECT 10 nDay
UNION
SELECT 11 nDay
UNION
SELECT 12 nDay
UNION
SELECT 13 nDay
UNION
SELECT 14 nDay
UNION
SELECT 15 nDay
UNION
SELECT 16 nDay
UNION
SELECT 17 nDay
UNION
SELECT 18 nDay
UNION
SELECT 19 nDay
UNION
SELECT 20 nDay
UNION
SELECT 21 nDay
UNION
SELECT 22 nDay
UNION
SELECT 23 nDay
UNION
SELECT 24 nDay
UNION
SELECT 25 nDay
UNION
SELECT 26 nDay
UNION
SELECT 27 nDay
UNION
SELECT 28 nDay
UNION
SELECT 29 nDay
UNION
SELECT 30 nDay
UNION
SELECT 31 nDay
) tDays
cross join
(SELECT 2008 AS nYear) nYears
where
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) = 1 AND
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) BETWEEN @Start AND @Stop
RETURN
END
March 9, 2009 at 3:01 pm
[font="Verdana"]One issue I can see: not all months have the same number of days (31st of February, anyone?). Why not encode the first and last day with the month list? Then you could add a between to the where clause to filter out invalid days. The issue then arises with leap years.
You might be better off just counting year days, rather than months and days, and use datepart() and datename() to pull out the appropriate bits.
BTW, get in the habit of using UNION ALL, not UNION (unless you specifically need the filtering of UNION.)
[/font]
March 9, 2009 at 3:04 pm
This is odd. Somebody smarter than me will have to give you the answer on this one. If you look at the
Estimated Execution Plan, it looks like the entire table is built and then the next to last operation is a
Compute Scalar that is applying ALL the criteria in one step, but unfortunately because of this the IsDate is
not applied in time.
I'm attaching the Estimated Execution Plan from my 2005 SP3 box.
One other note, could you format the code so it is a little narrower, it doesn't even fit in my 22" widescreen
without a horizontal scroll bar.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 3:09 pm
[font="Verdana"]When you use an expression in the form x AND y, SQL Server can "lazy evaluate" by saying "if the first part is false, I don't need to even look at the second part." This is what you are relying.
However, there's no guarantee that SQL Server won't recorder, as x AND y is equivalent to y AND x. So it may be doing that to pull out common sub-expressions within the two parts of the code.
I think it would be better just to generate valid dates to start with.
[/font]
March 10, 2009 at 7:23 am
Bruce W Cassidy (3/9/2009)
[font="Verdana"]One issue I can see: not all months have the same number of days (31st of February, anyone?). Why not encode the first and last day with the month list? Then you could add a between to the where clause to filter out invalid days. The issue then arises with leap years.You might be better off just counting year days, rather than months and days, and use datepart() and datename() to pull out the appropriate bits.
BTW, get in the habit of using UNION ALL, not UNION (unless you specifically need the filtering of UNION.)
[/font]
Written a long time ago on SQL 7 so has been that way thru 2000. The IsDate funtion check removes any invalid dates before I do my processing so leap years are covered. Will consider changing UNION to UNION ALL to avoid the filtering step as suggested, thanks.
March 10, 2009 at 7:30 am
One other note, could you format the code so it is a little narrower, it doesn't even fit in my 22" widescreen
without a horizontal scroll bar.
Fixed formatting.
March 10, 2009 at 7:42 am
Jack Corbett (3/9/2009)
This is odd. Somebody smarter than me will have to give you the answer on this one. If you look at theEstimated Execution Plan, it looks like the entire table is built and then the next to last operation is a
Compute Scalar that is applying ALL the criteria in one step, but unfortunately because of this the IsDate is
not applied in time.
Looks like you have found the issue. I removed 29-31 as possible day options for the month and ran without an issue. However them does seem an odd behavior since the where inside there should be applied for the table to be derived correctly. Even when I wrap that into another derived table the issue seems to be DATEPART is applied before the IsDate. I do have a workaround but will submit to MS for more feedback. Thanks
March 10, 2009 at 7:53 am
Antares686 (3/10/2009)
Jack Corbett (3/9/2009)
Estimated Execution Plan, it looks like the entire table is built and then the next to last operation is a
Compute Scalar that is applying ALL the criteria in one step, but unfortunately because of this the IsDate is
not applied in time.
Looks like you have found the issue. I removed 29-31 as possible day options for the month and ran without an issue. However them does seem an odd behavior since the where inside there should be applied for the table to be derived correctly. Even when I wrap that into another derived table the issue seems to be DATEPART is applied before the IsDate. I do have a workaround but will submit to MS for more feedback. Thanks
It appears that the Query Optimizer is deciding that it is faster to generate all the rows for the derived table and then apply the filters. I even tried putting the outer derived table, tBase, in a CTE to see if that would force the order and it did not.
I just tried it against a 2008 install and the result is the same as 2005. The query plan still does the derived table(s) and then applies the filters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 8:43 am
please confirm that the purpose of the function is to return a table of dates between a given StartDate and EndDate inclusively, ie if StartDate were 2009-01-01 and EndDate were 2009-02-01, the dates resturned would be from 2009-01-01 through 2009-02-01.
March 10, 2009 at 8:59 am
This change to the inner WHERE clause will fix your problem. Conditions in a CASE statement are evaluated in order, so only values that pass the ISDATE checks will get to the date range check where your code is failing.
where
case
when
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) is null
then 0
when
IsDate(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
) <> 1
then 0
whennot
cast(
cast(nYear as varchar(4)) +
right('0' + cast(nMonth as varchar(2)),2) +
right('0' + cast(nDay as varchar(2)),2)
as datetime) BETWEEN '20080101' AND '20081231'
then 0
else 1
end = 1
March 10, 2009 at 9:05 am
Here is another way to generate the dates you are generating.
CREATE FUNCTION dbo.DatesInRange(@StartDate datetime, @StopDate datetime)
RETURNS TABLE
AS
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4)
select top (datediff(dd, @StartDate, @StopDate) + 1)
dateadd(dd, N - 1, @StartDate) as Dates
from
Tally;
go
select * from dbo.DatesInRange('2008-01-01','2008-12-31');
go
DROP FUNCTION dbo.DatesInRange;
go
March 10, 2009 at 9:46 am
Lynn Pettis (3/10/2009)
please confirm that the purpose of the function is to return a table of dates between a given StartDate and EndDate inclusively, ie if StartDate were 2009-01-01 and EndDate were 2009-02-01, the dates resturned would be from 2009-01-01 through 2009-02-01.
Yes inclusively, which works in 2000 and works using the User Defined Function to create the output.
March 10, 2009 at 9:57 am
I think you will find that the SQL Server 2005 version of the UDF I wrote will work just as well. It may even be more efficient as I have written it as an in-line function instead of as a multi-statement TVF as you had originally. The only way to know for sure is to test it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply