using isnull() for date fields

[rq]

In my SELECT statement, I have a date field in which at times it will have NULL values. If I use the ISNULL() function, can I define the replacement value (i.e., the second parameter in the ISNUll function) to be a blank date field -- as in ' / / '?

 

[Corran007]

What exactly are you trying to do?

 

[andremc]

Tried this several ways and
it always displays as 1900-01-01 00:00:00.000.

andremc

 

[TheBugSlayer]

OK. This is my third attempt at answering your question and I think I just got it.

The answer would be yes if you treat (define) your date field as char or varchar, in which case SELECT IsNull(MyDate, ' / / ') will do what you want.

Hope this helps.

 

[rq]

Corran007...I created a view that is used by Data Transformation Services to export results into a dbf format. Within that view, I would like to represent NULL dates as a blank date field.

 

[rq]

Thanks, BugSlayer !!! That did the trick...

 

[SQLBill]

rq,

Remember this:

1. DATETIME datatype ALWAYS is a date AND a time. If one or both are not provided MS SQL Server inserts the default.

2. You cannot use dates/times with DATE functions (DATEADD, DATEDIFF, etc) when they are VARCHAR, NVARCHAR, CHAR, NCHAR. To use DATE functions, the date and time MUST be DATETIME datatype. If you have them stored as VARCHAR, etc. you will need to convert/cast the data before using the DATE functions. Again, NULL cannot be converted/cast into DATETIME datatype.

-SQLBill