Menu
I have a table which contains 'NULL' values which are of type 'Datetime'. Now i have to convert those into empty string but when when i use convert function
ISNULL( [Accrued Out of Default] ,'' ) here accrued into default is of datetime type, what it does it changes null to '1900-01-01 00:00:00.000' instead of empty
Then i try to convert them into varchar and apply same
ISNULL(CONVERT(varchar(50), [Amort Into Default] ),'') Now I am able to convert into empty string but now those datetime are converted to string which I needed in datetime So I try to CAST, CONVERT but non of them works.
CONVERT(Datetime,'ISNULL(CONVERT(varchar(50), [Amort Into Default] ),'')',120) This gives error.
Is there any possible solution to this.
> **Solution Hi someone answered this to do as. > ISNULL(CONVERT(varchar(50), [Amort Into Default] ,120),'') and it works I dont know why . **
477
There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.
We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL.
Try using the function IS_SPACES(). If your source also has NULL values then use IS_NULL() function and route the data accordingly. Adding to what Pallavi and Prashant mentioned, there are two actions you will need to do, Identify the NULLS and replace it with an absolute date value or NULL.
CASE and CAST should work:
CASE WHEN mycol IS NULL THEN '' ELSE CONVERT(varchar(50), mycol, 121) END using an ISNULL is the best way I found of getting round the NULL in dates :
ISNULL(CASE WHEN CONVERT(DATE, YOURDate) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10), YOURDate, 103) END, '') AS [YOUR Date] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
