Select Query and Update - 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

Select Query and Update

Author

Topic

prasad332
Starting Member

3 Posts

Posted - 2007-11-20 : 14:00:30

Hi Friends,

I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null.

Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31

Regards,

Prasad K

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 14:46:47

I am fairly sure IsDate is arround in SQL Server 2000...
[CODE]
SELECT Count(*)
FROM mytable
WHERE IsDate(dateFiled) = 1
[/CODE]

George
<3Engaged!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-21 : 01:22:02

Note that isdate() is not always reliable

Select isdate(2006), isdate('2006'),isdate(200600/100)

You should also check its length

WHERE IsDate(dateFiled) = 1
and LEN(dateFiled)=10

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources