Check if field is all date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Check if field is all date

Author

Topic

Huascar82
Starting Member

6 Posts

Posted - 2008-01-18 : 11:13:59

Hey all,

I've been ehre off and on when I've needed help. And I know someone answered my question last time with the exact info I needed. But I seem to have lost the query I saved.

I pretty much need a query that can filter a field I have adn pull up any records that are not in in date format. My current field is text and I'm trying to convert it over to DateTime but am getting stuck. Seems at least one of the records does not have a date format.

Thank you all.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-18 : 11:18:52

[code]Select * from table where IsDate(col) = 0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 11:20:15

Use

ISDATE(Field)=1 and LEN(Field)>=8

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-21 : 09:48:28

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-21 : 14:20:13

Please go through the code ..... here Field is the column(Datatype: Text)

"SELECT ISDATE(CAST(CAST(Field AS VARCHAR(20)) AS DATETIME) FROM Table"

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-21 : 16:05:40

>>pull up any records that are not in in date format
perhaps a combination of 2 of the previous solutions:


select col from tb where isDate(col) = 0
union 
select col from tb where isDate(col) = 1 and len(col) < 8

subrata4allfriends,

quote:
Originally posted by subrata4allfriends

Please go through the code ..... here Field is the column(Datatype: Text)

"SELECT ISDATE(CAST(CAST(Field AS VARCHAR(20)) AS DATETIME) FROM Table"

You should probably "...go through the code..." yourself before you post a solution

( besides missing a ")" ) Your second CAST will fail when the value is not a valid datetime.

SELECT ISDATE([CAST(CAST(Field AS VARCHAR(20)) AS DATETIME)

also, as madhivanan and visakh16 eluded to, ISDATE can lead to false positives.


SELECT [field]
	,[isDate] = ISDATE(CAST(CAST(Field AS VARCHAR(20)) AS DATETIME) )
FROM	(
	select '2008-01-21' as [field] 
	union all select '2008'
	union all select 'tg'
	) a

output:
            
field      isDate      
---------- ----------- 
2008-01-21 1
2008       1

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources