SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Statement Not Working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kramerd1506
Starting Member

8 Posts

Posted - 11/16/2012 :  13:30:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:43:09  Show Profile  Reply with Quote
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;

Edited by - sunitabeck on 11/16/2012 13:44:32
Go to Top of Page

kramerd1506
Starting Member

8 Posts

Posted - 11/16/2012 :  17:59:19  Show Profile  Reply with Quote
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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 11/17/2012 :  03:39:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/17/2012 :  09:51:46  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000