October 4, 2016 at 1:05 pm
The following segment of code lives in a long SQL stmt in the WHERE CLAUSE
CONVERT(DATE, Getdate())
NOT BETWEEN
Cast( Cast( monthbegindate AS VARCHAR ) AS DATETIME)
AND
Cast( Cast( monthenddate AS VARCHAR) AS DATETIME)
PROBLEM: Sometimes the monthbegindate and monthenddate can have values that are strictly not in the YYYYMMDD format.
Is there a way to rewrite this piece.
I would use a CASE WHEN ISDATE(monthbegindate) = THEN ELSE NULL for monthbegindate and monthenddate
But then ?? This is where I am having an issue.. Tyring to restore the original filter
October 4, 2016 at 1:48 pm
No need to respond.. I found my own solution.
(
CONVERT(DATE, Getdate())
NOT BETWEEN
CASE WHEN ISDATE(monthbegindate) = 1 THEN
Cast( Cast( monthbegindate AS VARCHAR ) AS DATETIME)
ELSE
GETDATE() - 2
END
AND
CASE WHEN ISDATE(monthenddate) = 1 THEN
Cast( Cast( monthenddate AS VARCHAR ) AS DATETIME)
ELSE
GETDATE() + 3
END
)
October 4, 2016 at 1:50 pm
What datatype are your monthbegindate and monthenddate in that they need to be converted to VARCHAR before being converted to DATETIME?
Why are you converting monthbegindate and monthenddate to DATETIME rather than DATE?
What are the formats that are not strictly in YYYYMMDD format?
You should specify the length when converting to VARCHAR. You could potentially be truncating data without realizing it and it can be very hard to troubleshoot this.
You could use TRY_CAST instead of CAST. It will attempt to perform the cast, and will return NULL (instead of an error) if it is unable to do so.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2016 at 2:01 pm
Quite unusual but our as400 system stores dates as integer.
Anyhow my solution works for the time being
October 4, 2016 at 2:02 pm
You could do this:
WHERE CONVERT(DATE, Getdate())
NOT BETWEEN TRY_CONVERT(monthbegindate, datetime) AND TRY_CONVERT(monthenddate, datetime)
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply