March 12, 2012 at 12:16 pm
Ldate is varchar type,
it contains values '11/02/01' and 'ZZZZZZ'
I need record only if value is date format, that is why in CTE I check isdate(Ldate) = 1
1st I select only record with value '11/02/01'
2nd from CTE I convert Ldate to int
but WHERE gives an error: Conversion failed when converting the varchar value 'ZZZZZZ' to data type int.
--> T-SQL:
with d (Ldate) as (select Ldate from dbo.Table where isdate(Ldate) = 1)
SELECT *
FROM d
WHERE cast((right(ldate,2) + substring(ldate,4,2) + left(ldate,2)) as int) > 010101
------------------------------------------
T-SQL works only If I put result in temp table and then run query
as:
select Ldate
into #T
from dbo.Table where isdate(Ldate) = 1
GO
SELECT *
FROM #T
WHERE cast((right(ldate,2) + substring(ldate,4,2) + left(ldate,2)) as int) > 010101
GO
March 12, 2012 at 12:46 pm
How about something along these lines:
create table dbo.test1 (
tid int identity(1,1) not null,
ldate varchar(8) not null);
insert into dbo.test1 (ldate)
select '11/02/10' union all select 'ZZZZZZ';
insert into dbo.test1 (ldate)
select '11/02/01' union all select '11/02/00';
select * from dbo.test1;
with datesonly as (
select
case when isdate(ldate) = 1 then cast(ldate as datetime) else cast('19000101' as datetime) end ldate
from
dbo.test1
where
isdate(ldate) = 1
)
select
*
from
datesonly
where
ldate >= cast('01/01/01' as datetime)
;
drop table dbo.test1;
March 12, 2012 at 12:48 pm
Using the case, you could eliminate the CTE as it may not be needed. It would help, of course, if we actually knew what you were trying to accomplish instead of just a small piece of it.
March 14, 2012 at 8:00 am
Use proper datatypes. Store date in date (not in varchar), int in int, etc.
If you already have it wrong (dates in varchar field), why do you try to convert them to int (another wrong data type)?
Nevertheless, is_date() is not reliable:
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
Try to filter out various date formats with LIKE, and then use CONVERT with proper format number to convert to datetime.
E.g.
SELECT DateAsDatetime = CONVERT(datetime, t.DateStoredInVarchar, 1) -- 1 = MM/DD/YY
FROM dbo.YourTable t
WHERE t.DateStoredInVarchar like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]' -- NN/NN/NN
AND ISDATE(t.DateStoredInVarchar)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply