Error Converting datetime to 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

General SQL Server Forums

New to SQL Server Programming

Error Converting datetime to string

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2011-08-29 : 16:15:48

Hi
I have an excel spreadsheet, in a column i have a datetime field,
All of the other date time fields are working using this code; however, when i get to this column I am getting an error.

The following code is giving me an error.

case WHEN len([Closing Memo Date]) = 0 THEN null
ELSE [Closing Memo Date]
END AS [Closing Memo Date],

ERROR: Converting Datetime to String
I looked at the values in this column and sometimes it is blank, sometimes it is null, sometimes it has a value.

I am trying this code but it does not work, I keep getting a syntax error:

case isnull([Closing Memo Date],' ') WHEN len([Closing Memo Date]) = 0 THEN null --or len([Closing Memo Date]) = 0 THEN null
ELSE [Closing Memo Date]
END AS [Closing Memo Date],

what I am trying to do is handle the case when the [Closing Memo Date] is null - turn it into a string then apply the len function or basically,

if the field is null leave it alone and continue, if it is a blank string then convert it to a null and continue processing

Any help would be great

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-29 : 16:39:17

What is the datatype of [Closing Memo Date]? Datetime? Varchar?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-29 : 16:41:35

Assuming your code is running in MS Sql Server
Use a "searched" CASE rather than a "simple" CASE (also added a check for invalid date values):


select
       case 
       when len([Closing Memo Date]) = 0 THEN null
       when isDate([Closing Memo Date]) = 0 THEN null
       ELSE [Closing Memo Date]
       END AS [Closing Memo Date]

Be One with the Optimizer
TG

zwheeler
Starting Member

25 Posts

Posted - 2011-08-29 : 16:48:50

Thank you that worked.
? that is called a search case?
thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-29 : 16:55:30

You're welcome - and yes it is:
Case

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources