Sql Server-How to Replace a Date Column With a Current Date When the Column Has a Null Value

How can replace a null value in a date column with "0"

declare @dt datetime = '2016-07-11'
declare @dt2 datetime = null

select case when @dt is null then '0' else cast(@dt as varchar(11)) end dt,
       case when @dt2 is null then '0'  else cast(@dt2 as varchar(11)) end dt2

returns

dt          dt2
Jul 11 2016 0

How to Pass current date if there is null value in the field?

Use IsNull()

Example

Declare @YourTable table ([Installation Date] datetime)
Insert into @YourTable values
('2018-05-22 12:18:22')
,(null)

Select FORMAT(IsNull(a.[Installation Date],GetDate()), 'G') 
 From @YourTable A

Returns

5/22/2018 12:18:22 PM
9/4/2018 12:14:51 PM

Convert date column from varchar to date type when column has null values in sql

The correct format to convert your strings to dates is 101 (mm/dd/yyyy).

So first try with a SELECT statement to convert:

select convert(date, Date_col, 101) Date_col from df

If you get any errors then the date strings are not correct dates.

If there is no problem then update the table:

update df
set Date_col = convert(date, Date_col, 101)

and change the data type of the column:

alter table df
alter column Date_col date

See the demo.

Update date column with current date where NULL

From https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

To test for NULL, use the IS NULL and IS NOT NULL operators

Try this:

UPDATE videos SET publication_date = now() WHERE publication_date IS NULL

updating date to null

The error appears to be telling you that your data model defines enroll_date as a NOT NULL column. You cannot, therefore, set it to be NULL.

You could modify the table definition to allow NULL values

ALTER TABLE enrollment
  MODIFY( enroll_date DATE NULL )

It seems likely, however, that this was an intentional choice made when defining the data model that should not be altered. I don't see how it would make sense to have an enrollment without having an enroll_date.

SQL Date Return If Null Then Blank

In SQL Server:

Since you want to use an empty string instead of null, then you are going to be converting to a string. You can use the size of 10 along with style 120 to return iso format (without the time portion) like so:

select 
    dob = isnull(convert(varchar(10),BirthDateTime,120),'')
  , [Admission Date] = isnull(convert(varchar(10),ServiceDateTime,120),'')
  , [Discharge Date] = isnull(convert(varchar(10),DischargeDateTime,120),'')
from ...

You can find the other style codes for convert() here: docs: cast and convert

---

Related Topics