Replace NULL with string

Asked

Viewed 1,228 times

1

In a datetime field, when the value is NULL should only return -, tried the function IFNULL 

SELECT ISNULL(data, '-') as data FROM tabela

but it returns me the error 

Microsoft OLE DB Provider for SQL Server error '80040e07'

Falha ao converter data e/ou hora da cadeia de caracteres.
sql-server

  • Marcelo, it would be possible to post your code?

  • Edited by, @Rubico

  • from what I’ve seen, you’re not using ifnull, but isnull

  • @Rubico also tried with him. The solution commented by Marconi solved.

2 answers

2

select COALESCE( CAST( DATA AS VARCHAR), '-') AS DATA from tabela

or 

select ISNULL( CAST( DATA AS VARCHAR ), '-') AS DATA from tabela

  • The proposed solution worked however converting the date to the format: ABR 28 2016 10:07AM. There are ways to leave in the pattern 28/04/2016 10:07 ?

  • 1

    @Marcelodeandrade tries to select COALESCE( convert(varchar,DATA,103), '-') AS DATA from tabela

  • Thus it returns only the date 28/04/2016, @Marconi

  • 2

    @Marcelodeandrade COALESCE( convert(varchar,DATA,103) + ' ' + SUBSTRING(convert(varchar,DATA,114),0,6), '-')

  • Yes, this way it worked properly @Marconi

  • @Marconi, if you can, create an answer for me to accept.

Show 1 more comment

1


Reply created as Requested @Marcelo.

Try:

 SELECT COALESCE(convert(varchar,DATA,103) + ' ' + SUBSTRING(convert(varchar,DATA,114),0,6), '-')

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.