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 2005 Forums
 Transact-SQL (2005)
 In a string field identify not date records?

Author  Topic 

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-15 : 16:24:47
Hi All,

I have a table called Table1 with a field called DateString with a nvarchar type. The records are like this;

DateString
1/11/2007
9/27/2006
2/22/2008
1/10/2007
11/13/2007
8/21/2007
5/23/2007
2/16/2007
12345678900
11/20/2006

It may not have date records which are just string like in this example is 12345678900. I need a SQL script which can identify those records which are not of date type. Please help.

Thanks,

Zee

Thanks a million..

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 16:25:53
Use the ISDATE built-in function. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-15 : 16:37:17
I got it.

select * from Table1 where isdate(dateString) = 0

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:59:15
ISDATE() is not that reliable.

Try

SELECT *
FROM Table1
WHERE Col1 NOT LIKE '[0-9]%/[0-9]%/[0-9][0-9][0-9][0-9]'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 01:22:16
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
Go to Top of Page
   

- Advertisement -