Error Converting Datetime - 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 2000 Forums

Transact-SQL (2000)

Error Converting Datetime

Author

Topic

BillyBaroo
Starting Member

2 Posts

Posted - 2005-01-21 : 17:23:57

Hello - I'm working with a table that has dates imported as VARCHAR and am receiving the error msg: 'Syntax error converting datetime from character string'. I'm trying to compare 2 dates together, but have to first change the datatype (via CAST). When doing this, I discover that the field that I'm using has values = 'N/A' in it. Any ideas how to get around this? I need to keep the table intact, so updating the 'N/A' values is not an option. My code:

SELECT TOP 10 *
FROM WorkDB..TableName
WHERE CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < (SELECT GETDATE())
AND BillDate <> 'N/A'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-21 : 17:28:27

try using the isDate function in your where clause:

SELECT TOP 10 *
FROM WorkDB..TableName
WHERE CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < (SELECT GETDATE())
AND isDate(BillDate) = 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-21 : 17:31:22

[code]

select top 10 *
from
(
select BillDate, EndDate
from @table1
where billdate <> 'N/A'
) t
where CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < GETDATE()

[/code]

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-21 : 17:32:14

IsDate is going to suffer the same problem. You need to exclude the N/A rows so that CAST doesn't run against invalid datetime data.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-21 : 17:33:03

Here's what I worked from:



declare @table1 table (billdate varchar(20), enddate varchar(20))

insert into @table1 values ('02-11-2004', '01-10-2004')
insert into @table1 values ('N/A', 'N/A')

select top 10 *
from
(
	select BillDate, EndDate
	from @table1
	where billdate <> 'N/A'
) t
where CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < GETDATE()

Tara

BillyBaroo
Starting Member

2 Posts

Posted - 2005-01-21 : 17:38:45

Brilliant!!
Thanks for the help Tara, you SQL Warrior Goddess

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-21 : 17:44:29

Yeah, good job, Tara.
I still think you should use the isDate function instead of <> 'N/A' in case other invalid values get in there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-21 : 17:48:14

Well yes you should do that. I was just saying you can't use it in the manner that you posted. You would have to put it like this:



select top 10 *
from
(
	select BillDate, EndDate
	from @table1
	where isDate(BillDate) = 1
) t
where CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < GETDATE()

You have to exclude the invalid dates from the result set prior to cast or convert running on them.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:41:25

BillyBaroo;
short-circuit evaluation makes your query quite correct;

compare

SELECT TOP 10 *
FROM WorkDB..TableName
WHERE CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < (SELECT GETDATE())
AND BillDate <> 'N/A'

to

SELECT TOP 10 *
FROM WorkDB..TableName
WHERE BillDate <> 'N/A' AND
CAST(BillDate AS DATETIME) > CAST(EndDate AS DATETIME)
AND CAST(BillDate AS DATETIME) < (SELECT GETDATE())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources