SQL Change Null date or 1-1-1900 00:00:00 to Current Date

Question

I googled a couple of hours so far, and tried many different things, but somehow what works with others doesn't seem to work on my dataset.

*EDIT: DBMS: SQL Server. + Code below

Let's say this is the dataset:

ID,VALIDFROM,VALIDTO

1,1-1-2012,1-1-1900 00:00:00

2,1-1-2016,1-3-2017 00:00:00

3,1-1-2017,1-3,2018 00:00:00

4,1-1-2017,1-1-1900 00:00:00

5,1-1-2018,1-1-1900 00:00:00

I want to convert the NULL's to Today's date, but whenever I try to do that with NULLIF or COALESCE, SQL returns 1-1-1900 00:00:00.

SELECT
A.CONTRACTLINEID,
A.CONTRACTID AS 'Contract ID',
COALESCE(C.RENTALCOSTTYPEID, 'LEEG') AS 'Component ID',
A.NAME AS 'Component',
A.LINETYPE AS 'Componenttype ID',
B.ENUMITEMLABEL AS 'Componenttype',
A.RENTALOBJECTID AS 'Vastgoed Object ID',
A.VALIDFROM,
--A.VALIDTO, -- 1-1-1900 00:00:00
--COALESCE(A.VALIDTO,Sysdate()) AS VALIDTO, -- ERROR
--COALESCE(A.VALIDTO,GETDATE()) AS VALIDTO, -- 1-1-1900 00:00:00
--COALESCE(A.VALIDTO,CURRENT_DATE) AS VALIDTO, -- ERROR
--NULLIF(A.VALIDTO,GETDATE()) AS VALIDTO, -- 1-1-1900 00:00:00
--NULLIF(A.VALIDTO,CURRENT_DATE) AS VALIDTO, -- ERROR
A.COSTSETTLEMENTID,
A.PRICEPERIODID,
A.DATAAREAID,
C.PRICEPRICEID AS 'Prijs ID',
COALESCE(C.PRICE, '0') AS 'Prijs',
C.FROMDATE AS 'Prijs Vanaf Datum',
C.TODATE AS 'Prijs T/m Datum',
COALESCE(C.NAME, 'LEEG') AS 'Component-Prijs',
D.CONTRACTSTATUS AS 'Contract Status'
FROM MRPMCCONTRACTLINES A
LEFT JOIN MRENUMS B ON
B.ENUMITEMVALUE = A.LINETYPE
AND B.ENUMID = 40021
OUTER APPLY
(
    SELECT  TOP 1 *
    FROM    MRPMCINVOICELINE C
    WHERE   C.CONTRACTLINEID = A.CONTRACTLINEID AND C.DATAAREAID = '1'
    ORDER BY
            C.TODATE DESC
    ) C
LEFT JOIN PMCCONTRACT D ON
D.CONTRACTID = A.CONTRACTID --Following doesn't do anything so far 
UPDATE MRPMCCONTRACTLINES
SET VALIDTO = '01/01/2050' 
WHERE VALIDTO IS NULL 
OR LTRIM(RTRIM(VALIDTO)) = ''

For questions like this, you should give more details like DB Vendor and version. Since you have used NULLIF, I suppose it is MSSQL DB, you may try select NULLIF(todate, getdate()) from table this would work for nulls. And hopefully may guide you for further. — Canburak Tümer, Apr 30 '18 at 19:39
`COALESCE` should work. Please show your code. And what is your DBMS? SQL Server? Oracle? PostgreSQL? ... — Thorsten Kettner, Apr 30 '18 at 20:14

score 4 · Answer 1 · answered Apr 30 '18 at 19:48

4

COALESCE() replaces NULL with a value so that is what you want. NULLIF() replaces a value with NULL and doesn't apply here.

My guess is you are doing COALESCE(TODATE,'') which will replace NULL with the default datetime of 1-1-1900 00:00:00.

Make sure you are feeding it today's date properly COALESCE(TODATE,GETDATE())

The function to get today's date will vary based on your DBMS. Always tag your question with your DBMS (SQL Server, MySQL, Oracle, etc..) to get the best answers.

answered Apr 30 '18 at 19:48

Aaron Dietz

10,137
1
14
26

I tried many different things now, one of them is COALESCE(A.VALIDTO,GETDATE()) AS VALID.TO, and it still returns 1-1-1900 00:00:00. I think the problem is that I work with PowerBI, and the 'nulls' are already converted to 1-1-1900 00:00:00 instead of NULL. So maybe there is a way to change a date of 1-1-1900 00:00:00 to GETDATE() or a random date in the future like 1-1-2050 00:00:00. – titatovenaar May 04 '18 at 05:22
@titatovenaar Ahh, that explains why you were trying `NULLIF()` too. Try changing the date to `NULL` then coalescing it back to today's date: `COALESCE(NULLIF(A.VALIDTO,'1-1-1900 00:00:00'),GETDATE())` – Aaron Dietz May 04 '18 at 14:05
1

Thanks! This is indeed the solution for my problem. Next time I'll try to be a bit less confusing. Cheers, Igor – titatovenaar May 08 '18 at 10:47

score 0 · Answer 2 · answered Apr 30 '18 at 19:38

0

you shouldn't be using NULLIF, its purpose is to compare values. If you want to substitute a value for a null in a select statement, you would use isnull (for SQL server) or ifnull (for MySQL)

answered Apr 30 '18 at 19:38

Mordechai

718
1
8
23

SQL Change Null date or 1-1-1900 00:00:00 to Current Date

2 Answers2