In a string field identify not date records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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/

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-15 : 16:37:17

I got it.

select * from Table1 where isdate(dateString) = 0

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"

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources