Working around DATEDIFF when "date" = ''00000000''
-
I have two "date" fields in a database, "ORIGREQDATE" and "NEWREQDATE" which have a datatype of CHAR and are formatted as YYYYMMDD.
The INSERT statement (which I cannot modify) inserts "Null" values as 00000000.
I am attempting to compare those dates against getdate(), and select data where the difference between the two dates is > 1.
My statement is
Select (blah blah blah) where DATEDIFF(D, CONVERT(DATETIME, NEWREQDATE), GETDATE() ) > 1).This works great, except when one of the fields = 00000000. In those instances, SQL throws the error"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."I have experimented with CASE and HAVING, and various subqueries, but SQL always wants to do the DATEDIFF function regardless. No Joy.
I could construct a temporary table and have my front end talk to that table, but would prefer not to do so.
I think this is what I am looking for, logically: Look at NEWREQDATE. If that field is NOT Null or 00000000, select the record IF DATEDIFF(D, CONVERT(DATETIME, NEWREQDATE), GETDATE() ) > 1). If NEWREQDATE is NULL or 00000000, look at ORIGREQDATE and select the record if DATEDIFF(D, CONVERT(DATETIME, ORIGREQDATE), GETDATE() ) > 1).
Any help would be greatly appreciated. MB
-
Not tested, and I was tempted to put a different case statement around the NEWREQDATE in your existing logic, but without knowing your reqs, it might or might not be a good idea. This should do the trick either way:
WHERE
0 < CASE NEWREQDATE
WHEN
'00000000'
THEN
1 -- Or use 0 (zero) if you want those to fail to match the where criteria
ELSE
DATEDIFF(D, CONVERT(DATETIME, NEWREQDATE), GETDATE() )
END
If it still tries to do the implicit conversion, let me know whether '00000000' should pass or fail the WHERE clause and we'll do it inline, which should work.
-
David,
Thanks for the reply. Sorry if my answer is obtuse, but I'm more experienced at front-end programming...in the WHERE clause, if NEWREQDATE = 000000000 THEN do the DATEDIFF on ORIGREQDATE, ELSE do the DATEDIFF on NEWREQDATE. Let me know if you'd like to take this thread offline.
Thanks
Mike
-
Is this what you want?
WHERE 1 =
CASE
WHEN NULLIF(NEWREQDATE, '00000000') IS NULL AND NULLIF(ORIGREQDATE, '00000000') IS NULL
THEN 0
WHEN NULLIF(NEWREQDATE, '00000000') IS NULL
AND DATEDIFF(d, CAST(ORIGREQDATE AS datetime), GETDATE()) > 1
THEN 1
WHEN NULLIF(NEWREQDATE, '00000000') IS NOT NULL
AND DATEDIFF(d, CAST(NEWREQDATE AS datetime), GETDATE()) > 1
THEN 1
ELSE 0
END
-
Ken,
That seems to work. Now off to BooksOnline to figure out what you did.
Thanks
Mike
-
I'm not Ken, but since we both used the same method, I'll see if I can explain it to you from a high level. First, WHERE doesn't allow things like: WHERE CASE blah blah blah END CASE, therefore we give it a literal on one side of the equal sign, a 1 in this case. Then, the goal is to return a 1 on the other side when we want the record returned, and something else (a zero here) when we don't want the record returned.
WHEN NULLIF(NEWREQDATE, '00000000') IS NULL AND NULLIF(ORIGREQDATE, '00000000') IS NULL
THEN 0
Here, Ken's saying that if both NEWREQDATE and ORIGREQDATE are null or '00000000', don't return this row. In other words, no date in either place, no row. Notice the "THEN 0", which doesn't match the 1 on the left hand side of the equal sign in the WHERE clause.
WHEN NULLIF(NEWREQDATE, '00000000') IS NULL
AND DATEDIFF(d, CAST(ORIGREQDATE AS datetime), GETDATE()) > 1
THEN 1
Here, he's saying that if NEWREQDATE is '00000000' or Null and ORIGREQDATE returns a difference greater than 1, return the row. Hence, the "THEN 1", which does match the 1 on the left hand side of the equal sign.
WHEN NULLIF(NEWREQDATE, '00000000') IS NOT NULL
AND DATEDIFF(d, CAST(NEWREQDATE AS datetime), GETDATE()) > 1
THEN 1
Here, NEWREQDATE is neither '00000000' nor is it Null, therefore check the diff using NEWREQDATE, and if it's greater than 1, return the rows. Hence the "THEN 1" again.
ELSE 0
Finally, he's saying that if none of the other conditions were met, return a 0, which doesn't match, therefore no rows. This is useful even if you can't imagine a circumstance in which the other conditions won't be met, as as soon as you think you've covered all of the bases is when you'll find out that you're mistaken. I sometimes even put a print statement in that final else, to let me know that the other conditions weren't met. Great for testing.
-
Another victim of NULL replacement.
You need just restore NULL where it suppose to be:
where DATEDIFF(D, CONVERT(DATETIME, NULLIF(NEWREQDATE, '00000000'), GETDATE() ) > 1
_____________
Code for TallyGenerator -
Since you have the dates stored in a character column, it might be faster to do string comparisons, instead of casting two columns to datetime to do the compares, especially if you have an index on them. Even if there are no indexes, it will avoid casting each column to a date.
This query will return data where the date is before yesterday.
select * from MyTable where ( NEWREQDATE between '17530101' and convert(varchar(30),getdate()-2,112) ) or ( ( NEWREQDATE is null or NEWREQDATE = '00000000' ) and ( ORIGREQDATE between '17530101' and convert(varchar(30),getdate()-2,112) )
Of course you know that it's a cardinal sin to store your dates in a character string, so I won't dwell on that. Go forth, and sin not more.
-
You can use NULLIF to change the '00000000' values back to NULL, but then you have to use ISNULL to replace the result with something > 1 if you want to select them.
WHERE
ISNULL(DATEDIFF(d, CONVERT(DATETIME, NULLIF(ORIGREQDATE, '00000000')), GETDATE()), 99999) > 1
OR ISNULL(DATEDIFF(d, CONVERT(DATETIME, NULLIF(NEWREQDATE, '00000000')), GETDATE()), 99999) > 1
-
Michael (and all)
Thanks to everyone for the replies. What a great learning (and teaching) tool web forums can be.
The great comedian/pianist Victor Borge said once, "It's YOUR language, I'm just trying to use it..."
I hold no claim to the design of this database. It's an old HP MPE/ix database that was ported over almost 'As Is' to SQL Server.
Fixed-length fields, packed fields, decimals stored as character fields, oh my...and since we're using a boxed app as the main front end, I cannot change any of the underlying structure. I am writing VB.NET front ends to handle things that the boxed app does not or cannot.Thanks again.
Mike
-
... and another illustration of why dates should be stored as dates, not character strings !!!!
-
Hi,
Date is a varchar field and it can contain some invalid dates also beside "0000000" So, You should Check for all invalid dates instead of checking "0000000".
Try this below mentioned Query and implement it as per your requirement :
Select Datediff(dd,[date],Getdate()) From
(
Select Case When Isdate([Date[)=0 then Null
Else [Date] End [Date] From
Table_Name
) a
Thanks & Regards,
Amit Gupta
/* Remove all physical & sychological barriers */
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply