need help in datetime conversion excluding invalid

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need help in datetime conversion excluding invalid

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-08-27 : 06:39:56

I have varachar(50) field which have date in yyyymmdd format
Now i have to filter the data which are greater then server date
I have following query
Select * from MDB_CONTENT_INFO
Where convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate()
Problem ist aht i may have some invalid data other then valid one.
I am getting error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
or
Conversion failed when converting date and/or time from character string.

I wanted to exclude those records but currently getting error for the query
as

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 06:50:59

look for ISDATE function on bol

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-08-27 : 07:53:47

Select * from MDB_CONTENT_INFO
Where ISDATE(convert(datetime, 'fdhgjdfgh',112)) = 1
and convert(datetime, 'fdhgjdfgh',112) >= GetDate()
but not working

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 08:00:59

[code]where ISDATE('fdhgjdfgh') = 1[/code]

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-08-27 : 12:57:09

After changing the query to
Select * from MDB_CONTENT_INFO
Where ISDATE(CON_SCHEDULED_INDUCTION_DATE) = 1
and convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate()
I have still same result

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-27 : 13:18:06

You can't do that in that way. There is no way to know which order SQL will evaluate the predicates.

I'm trying to remember how to do this in a reasonable fashion. I'd suggest getting your data fixed up before you load it into your database. But, try selecting the data into a temp table WHERE ISDATE() = 1. Then from there you should be able to filter by GETDATE().

So others might have some advice on how to do this better/esier/more effeciently.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-27 : 13:25:22

Maybe this will work?

WHERE
	convert
	(
		datetime, 
		CASE 
			WHEN ISDATE(CON_SCHEDULED_INDUCTION_DATE) = 1 THEN CON_SCHEDULED_INDUCTION_DATE
			ELSE NULL
		END,
		112
	) >= GetDate()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 07:45:31

or

Select * from MDB_CONTENT_INFO
Where ISDATE(CON_SCHEDULED_INDUCTION_DATE) = 1
and len(CON_SCHEDULED_INDUCTION_DATE)=8
and convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate()

Madhivanan

Failing to plan is Planning to fail

sam4all
Starting Member

6 Posts

Posted - 2010-08-31 : 15:17:09

kamii47,Madhivanan,Lamprey
None of the solution will work
Been there done this and it does not work....

As of now the only solution is to use temp table or update records with wrong dates to something else

kamii47

I have been having the same probelm and no owe has given me a solution yet
you can follow this in the thread below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149310

quote:
Originally posted by madhivanan

or

Select * from MDB_CONTENT_INFO
Where ISDATE(CON_SCHEDULED_INDUCTION_DATE) = 1
and len(CON_SCHEDULED_INDUCTION_DATE)=8
and convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate()

Madhivanan

Failing to plan is Planning to fail

atrofman
Starting Member

1 Post

Posted - 2011-06-24 : 22:07:55

Here is what I did.
1. I have created a scalar function (for the particular Data Base where I want to use convert(datetime,...) in conjunction with isdate():
_____________________________________________________________________________________
IF OBJECT_ID (N'dbo.convertDT', N'FN') IS NOT NULL
DROP FUNCTION dbo.convertDT;
GO

CREATE FUNCTION dbo.convertDT (@DATE varchar(20))
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Ddate datetime;
SET @Ddate=(Case when isdate(@DATE)=1 then CONVERT(datetime,@DATE) else null end)

RETURN(@Ddate);
END;
GO
_____________________________________________________________________________________________

2. Then, instead of using convert(datetime, YourString), I used dbo.convertDT(YourString)
You can also use it in the code with select statements.

Ark Trofman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources