Error converting datetime from character string

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 2000 Forums

Transact-SQL (2000)

Error converting datetime from character string

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2005-08-24 : 15:03:52

i have a table in which a date is stored as numeric 9 in format yyyymmdd. I am attempting to create a query that gets all the records within that table in which that date is older than 60 days. I keep getting the error:Error converting datetime from character string

Select * from registered
where dateadd(dd,60, substring(cast(dateUpdated as varchar(8)),5,2) + '/' + Right(cast(dateUpdated as varchar(8)),2) + '/' + Left(cast(dateUpdated as varchar(8)), 4)) < getdate()

any ideas?

Thanks

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-24 : 15:22:26

Perhaps you have bad data in a row.

Verify the data like this...


SELECT * -- List all rows with an invalid date
  FROM registered
  WHERE ISDATE(substring(cast(dateUpdated as varchar(8)),5,2) + '/' + Right(cast(dateUpdated as varchar(8)),2) 
    + '/' + Left(cast(dateUpdated as varchar(8)), 4)) = 0

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-24 : 22:44:46

To BenSwitzer: If you want use indexes on dateUpdated you should avoid any manipulations with this field. It is much better if your query will look like this:

Select * from registered
where dateUpdated >convert(varchar(8), dateadd(dd, -60, getdate()), 112)

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2005-08-25 : 10:15:59

I used the convert method. I did a < instead of > because i wanted all the ones who were less than that date.
THanks

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-25 : 10:26:50

quote:
Originally posted by BenSwitzer

I used the convert method. I did a < instead of > because i wanted all the ones who were less than that date.
THanks

As I read the query,

Select * from registered
where dateUpdated >convert(varchar(8), dateadd(dd, -60, getdate()), 112)

compares an integer (dateUpdated) to a character string (convert(varchar(8), dateadd(dd, -60, getdate()))

Explicit conversion of the varchar to numeric 9 might make the comparison less ambiguous.

Did you get around the Error converting datetime to character string? What was the problem?

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-25 : 10:40:19

SamC: ...Explicit conversion of the varchar to numeric 9 might make the comparison less ambiguous.

BenSwitzer: ...date is stored as numeric 9 in format yyyymmdd...

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2005-08-25 : 11:56:04

The query that vlad gave me is working. Although Im not very sure why my own methods did not work. I knew that I had bad dates so I also tried a query like this:
Select * from
(
Select * from registered where isdate(substring(cast(dateUpdated as varchar(8)),5,2) + '/' + Right(cast(dateUpdated as varchar(8)),2) + '/' + Left(cast(dateUpdated as varchar(8)), 4)) = 1
) alldates
where dateadd(dd,60, substring(cast(dateUpdated as varchar(8)),5,2) + '/' + Right(cast(dateUpdated as varchar(8)),2) + '/' + Left(cast(dateUpdated as varchar(8)), 4)) < getdate()

That should of effectively removed bad dates but I still got the error. So it looks like vlads technique is the correct one.

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-25 : 12:09:39

quote:
Originally posted by VladRUS.ca

SamC: ...Explicit conversion of the varchar to numeric 9 might make the comparison less ambiguous.

BenSwitzer: ...date is stored as numeric 9 in format yyyymmdd...

I'm not sure what you are pointing out here. dateUpdated is NUMERIC(9). It's being compared to a VARCHAR string in the query

Select * from registered
  where dateUpdated >convert(varchar(8), dateadd(dd, -60, getdate()), 112)

I was pointing out that the WHERE clause is comparing different datatypes NUMERIC(9) > VARCHAR(8), so an implicit conversion will occur. In this case, I suppose the VARCHAR(8) will be implicitly converted to NUMERIC(9).

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-25 : 12:30:17

Hi BenSwitzer,

I think your problem can be in DATEFORMAT. Run DBCC USEROPTIONS and check Value for dateformat.
Consider this sample:

SET DATEFORMAT YMD
declare @dateUpdated numeric(9)
set @dateUpdated = 20050825

select substring(cast(@dateUpdated as varchar(8)),5,2) + '/' + Right(cast(@dateUpdated as varchar(8)),2) + '/' + Left(cast(@dateUpdated as varchar(8)), 4)
-- 08/25/2005

print cast('08/25/2005' as datetime)

SET DATEFORMAT DMY
print cast('08/25/2005' as datetime)
/*
Server: Msg 242, Level 16, State 3, Line 11
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
*/

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-25 : 12:42:42

quote:
Originally posted by SamC

quote:
Originally posted by VladRUS.ca

SamC: ...Explicit conversion of the varchar to numeric 9 might make the comparison less ambiguous.

BenSwitzer: ...date is stored as numeric 9 in format yyyymmdd...
I'm not sure what you are pointing out here. dateUpdated is NUMERIC(9). It's being compared to a VARCHAR string in the query

Select * from registered
where dateUpdated >convert(varchar(8), dateadd(dd, -60, getdate()), 112)

I was pointing out that the WHERE clause is comparing different datatypes NUMERIC(9) > VARCHAR(8), so an implicit conversion will occur. In this case, I suppose the VARCHAR(8) will be implicitly converted to NUMERIC(9).

Hi SamC,

You absolutely right NUMERIC(9) > VARCHAR(8)!
But for BenSwitzer it is not important because hi store in NUMERIC(9) only 8 digits (YYYYMMDD)

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-25 : 13:02:17

quote:
Originally posted by VladRUS.ca

You absolutely right NUMERIC(9) > VARCHAR(8)!
But for BenSwitzer it is not important because hi store in NUMERIC(9) only 8 digits (YYYYMMDD)

It's a habbit I have that when I compare a character string to a numeric I like to do explicit conversions to avoid any ambiguity. I realize implicit conversions should be obvious to experienced developers, but there's no performance hit to be explict so why not...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources