November 11, 2011 at 7:14 am
Hey all,
I have this statement in a where
CASE WHEN ISDATE(PAD.AdditionalDetail1) = 1 THEN CONVERT(DATETIME, PAD.AdditionalDetail1,103) ELSE @Date1999 END >= @date2010
Definition of vars is such:
DECLARE @Date1999 AS DATETIME
SET @Date1999 = CONVERT(DATETIME,'1999-01-01',103)
DECLARE @date2010 AS DATETIME
SET @date2010 = CONVERT(DATETIME,'2010-12-01',103)
With the where statement the select fails with a error converting varchar (pad.additionalddetail1 is a varchar) to datetime.
If however i remove that bit from the where and put this into the select:
CASE WHEN ISDATE(PAD.AdditionalDetail1) = 1 THEN CONVERT(DATETIME, PAD.AdditionalDetail1,103) ELSE @Date1999 END
This does not error and comes back with all valid dates.
Any ideas?
Dan
November 11, 2011 at 9:59 am
The plot thickens.
If i do this
CONVERT(DATETIME, PAD.AdditionalDetail1)
or this
CONVERT(DATETIME, PAD.AdditionalDetail1,0)
The problem goes away. However if i do this
CONVERT(DATETIME, PAD.AdditionalDetail1,101)
or
CONVERT(DATETIME, PAD.AdditionalDetail1,100)
It still errors.
The field has dates that ares supposed to be in 103 format. The query has set dateformat dmy; at the start.
Dan
November 14, 2011 at 3:15 am
Hello and welcome to SSC!
The main reason that you are not getting any replies for your question, it that we don't have enough information from you to answer!
For starters, it seems that your DDL script has fallen off your post as has your readily consumable sample data. Or perhaps you were unaware of the benefits of providing these things?
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
For now, I think you're using CONVERT in the wrong context. If you want a more detailed answer with code to illustrate, ensure that you read this article[/url] and supply us with DDL and sample data.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply