August 29, 2011 at 2:17 pm
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
August 29, 2011 at 2:23 pm
What happens if you try:
SELECT
CASE
WHEN LEN(COALESCE([Closing Memo Date], '')) = 0 THEN NULL
ELSE [Closing Memo Date]
END AS ClosingMemoDate
FROM YourTable
August 29, 2011 at 2:31 pm
hi
i get the following:
Syntax error converting datetime from character string.
August 29, 2011 at 2:45 pm
i have tried a direct cast
select cast([Closing Memo Date] as datetime)
from OpenQuery(psclaims_linked,'Select * from [Sheet1$]');
and i get the same error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
August 29, 2011 at 2:53 pm
at least some of your column values are not blank strings and also not convertable to a datetime...might want to look at it with this:
SELECT [Closing Memo Date] As BadDate,*
FROM
YourTable
WHERE ISDATE([Closing Memo Date]) = 0
SELECT
CASE
WHEN ISDATE([Closing Memo Date]) = 0 THEN NULL
ELSE [Closing Memo Date]
END AS ClosingMemoDate
FROM YourTable
Lowell
August 30, 2011 at 7:47 am
the following code snippet worked:
Search Case
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]
thanks for your assistance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply