Help with Date part

Question

Help with Date part

sql.kishore1

SSC-Addicted

Points: 435
More actions
May 17, 2010 at 2:55 pm

#222693

create table TestTable
(id int,
date Varchar(20)
)
insert into TestTable values(2,'08/31/1984')
insert into TestTable values(1,'07/12/2005')
insert into TestTable values(3,'08/31/0002')-- in this row i inserted 0002 intentionally my data had some errors like that.
declare @olams int
declare @dob datetime
set @dob='08/31/1984'
select @olams = (select count(*) from TestTable where (@dob IS NULL) OR date = CONVERT(DATETIME,@dob,101))
Print @olams
why this query is not working .
i know that date coloumn in test table has invalid data(0002) so thats why its giving an error . so how can fix this
Any help is appreciated.
Thanks
Kishore

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

The error you are getting is due to the CONVERT statement. The conversion results in an invalid date, hence the error.

The best way to prevent this is to store the data as a date datatype. You've just uncovered why you should not store dates as character data. To fix your existing data, you'll need to identify invalid dates in your data and manually fix them. How large of a table are you talking about here?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sql.kishore1 SSC-Addicted Points: 435 More actions · Answer 2

Yes i had like 2lakh records. i cant change the datatype of table to datetime. i need to fix it through query only.

Thanks

Kishore

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 3

And just in case you were wondering why is "08/31/0002" an invalid date time: it's because the earliest date you can store in SQL2K5 is Jan 01, 1753: http://msdn.microsoft.com/en-us/library/ms187819(SQL.90).aspx

sql.kishore1 SSC-Addicted Points: 435 More actions · Answer 4

Yes i know the error is due to invalid date.

is there any way that we fix this using tsql code

Thanks

Kishore

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 5

I think your statement at the end of your post skipped my eyes. Apologies :blush:

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 6

I would suggest trying this (on a separate set of data first)

update TestTable set myDate = convert(varchar(20), getdate()-3,101)

where myDate <= '01/01/1753'

John Rowan SSC Guru Points: 56440 More actions · Answer 7

I would build out a dates table with character dates and then JOIN that back to your table to find date value that are out of range. You can then take those out of range values, decide what they should be, and edit them appropriately.

This solution contains a loop. This could be written completely set based if you have a tally table in your DB.

--==== Build out test table from OP's example:

declare @TestTable table (id int, date Varchar(20))

insert into @TestTable values(2,'08/31/1984')

insert into @TestTable values(1,'07/12/2005')

insert into @TestTable values(3,'08/31/0002')-- in this row i inserted 0002 intentionally my data had some errors like that.

DECLARE @Date datetime

DECLARE @CharDates TABLE (RowID int IDENTITY(1,1), CharDate varchar(10))

SET NOCOUNT ON

--==== Set you MIN date range here

SET @Date = '1980-01-01'

--==== Loop from MIN date to MAX date (Set in WHILE condition below)

WHILE @Date < '2011-01-01'

BEGIN

INSERT INTO @CharDates(CharDate)

SELECT CONVERT(varchar,@Date,101)

SET @Date = DATEADD(dd,1,@Date)

END

--==== Now that a character dates table is built out, JOIN it back to your

--==== table containing character dates and look for values that don't match

SELECT *

FROM@TestTable t

LEFT JOIN @CharDates c ON t.date = c.chardate

WHEREc.chardate IS NULL

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 8

rjv_rnjn (5/17/2010)
I would suggest trying this (on a separate set of data first)
update TestTable set myDate = convert(varchar(20), getdate()-3,101)
where myDate <= '01/01/1753'

This will not work. Your <= comparison will not catch the invalid dates. The way your code is written, SQL Server will do a character comparison between the Date column and the string value ('01/01/1753'). Besides that, the invalid date in the example may not be the only flavor of invalid date. What if someone entered '15/31/2010'?

The only way to accuratly compare the columns is to convert the date to a real datetime. As we all know, this will fail when an invalid date is encountered. To solve this, we need to identify a which dates are invalid.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sql.kishore1 SSC-Addicted Points: 435 More actions · Answer 9

Thanks John

but your solution is creating new table called @CharDates

say suposse if my base table has 2 lakh records each time the @charDates will also have 2 lakh records .

its an extra burden to maintain a new table.

i can understand the probelm.

Thanks

Kishore.

John Rowan SSC Guru Points: 56440 More actions · Answer 10

@Chardates is a table variable that will only be used at the time of the query to find the invalid dates. It will not need to be maintained.

Is this a one-time fix?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 11

John Rowan (5/17/2010)
rjv_rnjn (5/17/2010)
I would suggest trying this (on a separate set of data first)
update TestTable set myDate = convert(varchar(20), getdate()-3,101)
where myDate <= '01/01/1753'
This will not work. Your <= comparison will not catch the invalid dates. The way your code is written, SQL Server will do a character comparison between the Date column and the string value ('01/01/1753'). Besides that, the invalid date in the example may not be the only flavor of invalid date. What if someone entered '15/31/2010'?

True. I get the point. Thanks.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 12

sql.kishore1 (5/17/2010)
create table TestTable
(id int,
date Varchar(20)
)
insert into TestTable values(2,'08/31/1984')
insert into TestTable values(1,'07/12/2005')
insert into TestTable values(3,'08/31/0002')-- in this row i inserted 0002 intentionally my data had some errors like that.
declare @olams int
declare @dob datetime
set @dob='08/31/1984'
select @olams = (select count(*) from TestTable where (@dob IS NULL) OR date = CONVERT(DATETIME,@dob,101))
Print @olams
why this query is not working .
i know that date coloumn in test table has invalid data(0002) so thats why its giving an error . so how can fix this
Any help is appreciated.
Thanks
Kishore

If you just want to fix your query (so that it will run without fixing the data), you could use the IsDate function with a CASE statement, something like this:

select @olams = (select count(*) from TestTable

where (@dob IS NULL)

OR CONVERT(DATETIME,@dob,101) = CASE WHEN ISDATE(date)= 1

THEN date

ELSE ''

END)

Now, if the varchar version of the date is not a date, you'll be matching against '', so it won't choke on the comparison.

Rob Schripsema
Propack, Inc.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Heh... try the following, Rob...

SELECT ISDATE('2000')

You sure you want that as a valid date?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql.kishore1 SSC-Addicted Points: 435 More actions · Answer 14

sql.kishore1

SSC-Addicted

Points: 435

May 17, 2010 at 8:05 pm

#1168553

Thanks Guys. I got it now.