SQL Syntax to fix date data overflow error

[rallcorn]

I posted this on SQL Syntax to fix date data overflow error
(I'm new to PowerPivot and actually forgot the MrExcel.com had a PowerPivot forum )

When pulling data from AS400 tables in which date fields are blank, in the Table Import Wizard>Specify a SQL Query, the SQL stmt will validate properly and will import correctly within the Table Import Wizard design screen (the blank date fields populate with "1/1/0001 12:00:00 AM"), however, when PowerPivot trys to convert, the date field is a problem. I receive msg, "Data overflow converting to the data type for table xxxx column 'UPDATE_DATE'. The current operation was cancelled because another operation in the transaction failed."

I've found a couple of fixes in various posts, the problem is I don't know how to incorporate these fixes into the correct syntax within my SQL query stmt.
One suggested solution:
Select case when [STORE.CLOSED_DATE} = '1901/1/1' then null else [STORE.CLOSED_DATE] end as STORE.CLOSED_DATE from DATAWHSE.DWMIRROR.STORE STORE

Another solution was to "wrap" STORE.CLOSED_DATE in "to_char(STORE.CLOSED_DATE,'mm/dd/yyyy')

I do not know how/where to incorporate either of these in my SQL stmt.

Here's the SQL stmt (the problem fields are "STORE.OPENED_DATE" and "STORE.CLOSED_DATE", STORE.CLOSED_DATE_8 is OK because it is a string rather than a date )

SELECT STORE.STORE_NUMBER, STORE.CITY, STORE.STATE, STORE.DIVISION, STORE.REGION, STORE.DISTRICT, [COLOR=#ff0000][B]STORE.OPENED_DATE[/B][/COLOR], [COLOR=#ff0000][B]STORE.CLOSED_DATE[/B][/COLOR], STORE.CLOSED_DATE_8
FROM DATAWHSE.DWMIRROR.STORE STORE
WHERE (STORE.STORE_NUMBER>99)
ORDER BY STORE.STORE_NUMBER

I'd really appreciate any help you guys can offer. Thank you.

 

[BrianMH]

SELECT STORE.STORE_NUMBER, STORE.CITY, STORE.STATE, STORE.DIVISION, STORE.REGION, STORE.DISTRICT, STORE.OPENED_DATE, case when [STORE.OPENED_DATE} = '1901/1/1' then null else [STORE.OPENED_DATE] end as OPENED_DATE, STORE.CLOSED_DATE_8
FROM DATAWHSE.DWMIRROR.STORE STORE
WHERE (STORE.STORE_NUMBER>99)
ORDER BY STORE.STORE_NUMBER

 

[rallcorn]

Thank you, thank you , thank you!!!!! You don't know how much benefit this is!!! I had to tweak just a bit, but works wonderfully.