Blank dates coming up as weird dates instead of NULL in SQL

Question

I am importing data from postgres into sql server. Fields where data type is 'timestamp without (or with) time zone' i used datetimeoffset in sql table but couldn't create package with the same. So i changed the datatype as datetime. Now, when I import data,all the dates and time are coming perfectly alright but where there is no data ( blank) then its displayed as some weird date instead of NULL in my SQL table. Those weird dates are - 1899-12-30 00:00:00.000 1753-01-01 00:00:00.000

What am i missing and how to resolve this? Maybe that's a simple thing but I am quite new to SQL so apologies if its really easy but any help is appreciated.

Thanks, AP

see https://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 — Tanner, Feb 15 '18 at 11:30
Sounds like that the ETL process you are using is migrating the *`NULL`* as date 0, or similar. In SQL Server, `'17530101'` is the lowest date you can store in a `datetime` (`'18991230'` however, is equivilent to `-2`). If the odd dates are knowns, however, after the import, or during, you could use `NULLIF`. For example: `NULLIF(YourDateColumn,'17530101')`. — Thom A, Feb 15 '18 at 11:30
and https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server — Tanner, Feb 15 '18 at 11:31
Hi @Tanner ,Thanks for your prompt response. I am using SSMS import and export wizard to import data.So after import ,how to use NULLIF function to replace values in tables? Should i use ALTER TABLE command? — user8165644, Feb 15 '18 at 11:43

score 0 · Accepted Answer · answered Feb 15 '18 at 11:54

0

Suppose your meaningful data is from 2000 onwards, then run a update statement like:

update table
set column = NULL
where column < cast('01-01-2000' as datetime)

answered Feb 15 '18 at 11:54

Prabhat G

2,974
1
22
31

Thanks a lot Prabhat. It worked. Please let me know if I can do something while importing to solve this issue rather than updating table afterwards? Thanks again for your help. – user8165644 Feb 15 '18 at 12:26
@user8165644: are you using SSIS import and export or SSMS import data? – Prabhat G Feb 16 '18 at 06:29
Hi Prabhat, currently usimg SSMS but will use SSIS later... Thanks – user8165644 Feb 17 '18 at 14:14
If you're using SSIS, then certainly there's option to `retain null`. For SSMS, I'll have to check (no access to Sybase) – Prabhat G Feb 17 '18 at 19:15
1

Thanks a lot for your help @ prabhat – user8165644 Feb 20 '18 at 13:29

Blank dates coming up as weird dates instead of NULL in SQL

1 Answers1