SQL Statement Not Working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Statement Not Working

Author

Topic

kramerd1506
Starting Member

8 Posts

Posted - 2012-11-16 : 13:30:05

Hi all. Please examine the following code which is not working properly:

DECLARE
@today datetime,
@nextDate datetime,
@secondDate datetime,
@thirdDate datetime;

set @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) as datetime);
set @nextDate =
(SELECT
min(CAST(CONVERT(VARCHAR(10), b.promiseddate, 101) as datetime)) as promiseddate
FROM podetail a inner join po b on a.guidpo = b.guidpo
WHERE
b.promiseddate > @today);
select @nextDate;

set @secondDate =
(SELECT
min(CAST(CONVERT(VARCHAR(10), b.promiseddate, 101) as datetime)) as promiseddate
FROM podetail a inner join po b on a.guidpo = b.guidpo
WHERE
b.promiseddate > @nextDate);
select @secondDate;

Running this code produces 2 result sets, one field each, they are dates with zeros for the times, which is what I want. The problem is they are the exact same date. It's as if the where clause "b.promiseddate > @nextDate" is being ignored. I've tried moving the min() inside the convert() and everything else I can think of with no solution. The data IS there in the database to support the correct results, but the query is not behaving. Any help is greatly appreciated.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 13:43:09

If promiseddate has a time component also, the behavior you are seeing can happen. You can test that hypothesis easily by changing your query as shown in red

DECLARE @today DATETIME,
        @nextDate DATETIME,
        @secondDate DATETIME,
        @thirdDate DATETIME;

SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS DATETIME);

SET @nextDate = (
        SELECT MIN(
                   CAST(CONVERT(VARCHAR(10), b.promiseddate, 101) AS DATETIME)
               ) AS promiseddate
        FROM   podetail a
               INNER JOIN po b
                    ON  a.guidpo = b.guidpo
        WHERE  b.promiseddate  >= DATEADD(dd,1,@today)
    );
SELECT @nextDate;

SET @secondDate = (
        SELECT MIN(
                   CAST(CONVERT(VARCHAR(10), b.promiseddate, 101) AS DATETIME)
               ) AS promiseddate
        FROM   podetail a
               INNER JOIN po b
                    ON  a.guidpo = b.guidpo
        WHERE  b.promiseddate  >=DATEADD(dd,DATEDIFF(dd,0,@nextDate),1)
    );
SELECT @secondDate;

kramerd1506
Starting Member

8 Posts

Posted - 2012-11-16 : 17:59:19

Thank you very much, that works great!

My next question is why? I thought I was completely removing any behavior around the times by going through the CAST and CONVERT procedure to only deal with the dates. The general consensus online is that the CAST and CONVERT "strips" the times off of the dates.

Thanks again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 03:39:17

Those not only strips off time but make your datetime into varchars where it leats to the problem

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-17 : 09:51:46

You stripped of the time part from @today and @nextday, but not from the promiseddate. So consider the statement

b.promiseddate > @today

. If today is November 17, 2012, @today would be '2012-11-17 00:00:00.000'. Now, if promiseddate is '2012-11-17 09:50:33.837', the condition would be satisfied, and you get '2012-11-17 00:00:00.000' as the result for @nextdate after stripping of the time part.

With that @nextdate, when you calculate @seconddate, the same thing happens. So it looks like it is not advancing the date at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources