converting datetime to varchar in a query

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

SQL Server Development (2000)

converting datetime to varchar in a query

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-01-09 : 15:33:34

This is the query I'm trying to execute:

select EffectiveDate = (case when EffectiveDate in ('', ' ', 'NULL', NULL) then cast(EffectiveDate as varchar(30)) else EffectiveDate end) from Central_Table where ID = 10

However, the resulting error I get is:

"Syntax error converting datetime from character string."

I know the case statement syntax works. EffectiveDate is of type DateTime, of course. I need to make it a string, though. What am I doing wrong?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-09 : 15:41:54

if it is a datetime, then your string comparisons are not only invalid, but unnecessary. try

SELECT convert(varchar(30), EffectiveDate)
from Central_Table
where ID = 10

OR

SELECT Cast(EffectiveDate as Varchar(30))
from Central_Table
where ID = 10

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-01-09 : 15:53:03

Yeah, thanks. The select statements you picked work. The problem is some of the values for EffectiveDate may be NULL or actually have a blank space (don't ask me why, I'm just the messenger). So, what I want to is if the field value has a space (' '), for instance, then I want it to return a string value of "Does not Exist". Maybe I can use a combination of ISNULL and CONVERT to accomplish this goal?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-09 : 15:57:51

quote:
Originally posted by SQLIsTheDevil

Yeah, thanks. The select statements you picked work. The problem is some of the values for EffectiveDate may be NULL or actually have a blank space (don't ask me why, I'm just the messenger). So, what I want to is if the field value has a space (' '), for instance, then I want it to return a string value of "Does not Exist". Maybe I can use a combination of ISNULL and CONVERT to accomplish this goal?

You have better string functions at the front end to do that kind of checks..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 16:08:28

Your display may be appearing as a space but a nullable datetime column can only contain a valid datetime value or a null.
go to a query window, switch your results to "text mode" instead of "grid mode" and run your query again.

dinakar is right that you should control the display on the frontend but to achieve that result from sql you were on the right track:
select isNull(convert(varchar, Effective_date), 'Does not exist')

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 02:13:17

As TG said, datetime column cant have any values other than proper date values or NULL

select dates from
(
select getdate() as dates union all
select '' union all
select ' ' union all
select NULL
)as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources