Date conversion error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date conversion error

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 05:03:27

I want to change a column type from char to datetime. When I try this I get an "... resulted in an out-of-range datetime value" error.

How can I locate this faulty input row(s) please? The table has 6.7 million rows, so I don't want to page through by hand.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-18 : 05:14:50

try...

where isdate(datecolumn) = 0

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 05:28:29

quote:
Originally posted by elancaster

try...

where isdate(datecolumn) = 0

Em

You cant always find faulty rows like this. The question is what format dates you are interested in. ISDATE() function will return 1 for whatever data it can convert to date. For example try,

SELECT ISDATE(2008)
so you must first decide on what type of values you are interested in before applying this.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 05:36:36

Does ISDATE() know about leap years?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-18 : 05:38:00

true, although for an 'out-of-range' type error, it would find those rows, no?
that's why i said 'try'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-18 : 05:38:58

just tried it and yes, leap years are fine

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:47:53

If most dates are supposed to have a fixed length, also try

SELECT *
FROM Table1
WHERE LEN(Col1) NOT BETWEEN 8 AND 10
OR ISDATE(Col1) = 0
OR Col1 IS NULL

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 06:37:00

I tried to automate this process by adapting an existing script. But after adding the line indicated it ceased to operate. Can anyone see why please?


DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'transactions' 
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + ISDATE(QUOTENAME(@col)) + ' = ''0'' THEN NULL ***** ADDED LINE ****
WHEN ' + QUOTENAME(@col) + ' LIKE ''13%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''12%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''11%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''9%'' THEN ''199'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''8%'' THEN ''198'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''7%'' THEN ''197'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ' + QUOTENAME(@col) + '
END

'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 06:46:24

[code]WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 THEN NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 07:12:20

quote:
Originally posted by Peso

WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 THEN NULL
E 12°55'05.25"
N 56°04'39.16"

Much obliged Peso.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 07:33:08

Out of interest, could my script be modified to change the column type to datetime after it has performed the string manipulation?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 09:20:23

ISDATE() is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-19 : 06:01:17

quote:
Originally posted by madhivanan

ISDATE() is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Great point. Many thanks for the tip (and link).

My dates are from a csv conversion. So, I need check their general format for any rubbish in order that SQL can convert them to datetime for meaningful date-based queries later. Some of the garbage I've come across so far ...ha! ... I could write a book.

I thought it was going well until I spotted a column entitled 'monkey' instread of monthly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources