Incorrect syntax near 'cast'

Can't figure out w开发者_StackOverflowhy I get the error

substring(cast(CASE WHEN l.assigned IS NOT NULL THEN l.assigned ELSE l2.assigned END), 0, 17) as [TEST1]

UPDATED:

The original line I was trying to tweak is the following...wanted to take out that convert because I don't want that format for the datetime:

, else substring(convert(varchar, cast(CASE WHEN l.assigned IS NOT NULL THEN l.assigned ELSE l2.assigned END as datetime), 126), 0, 17) end) as varchar(20)) as [TEST1]

Also I tried this which I forgot to mention

substring(cast(CASE WHEN l.assigned IS NOT NULL THEN l.assigned ELSE l2.assigned as datetime END), 0, 17) as [TEST1]

Error: Incorrect syntax near the keyword 'as'.

You get an error because you didn't specify a data-type as detailed here. The syntax goes something like this:

select cast('1/1/1911' as datetime)

You can more simply express your query using isnull (or coalesce) and left. Keep in mind that unless you're looking for a specific datetime format, you can just do the following:

select left(isnull(l.assigned,l2.assigned),17) as [TEST1]

CAST needs an AS datatype that appears to be missing.

e.g.

SELECT SUBSTRING(CAST(
         CASE WHEN 'foo' IS NOT NULL THEN 'foo' ELSE 'FOO' END AS VARCHAR(100))
       , 0, 17) AS [TEST1]

You can also use ISNULL or COALESCE to shorten your code a bit.

SELECT SUBSTRING(CAST(COALESCE(l.assigned,l2.assigned) AS VARCHAR(100)), 0, 17) 
                                                                       AS [TEST1]

To what data type you are casting? See reference:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

You are missing 'as' clause and data type.

You need to cast it to a type.

msdn: CAST and CONVERT