PS Query & SQL

Hi all, what a great group this is! I've gotten some great information reading through the posts already! 

Question from one of our users. In PS query (SQL server), he's writing an expression as a date type, and he wants the query result to be blank for the date field if the result of the expression is null. 

For example, let's say I have multiple fields that could store the date I want, and I have an expression that is if the date1 is not null use date1, if date2 is not null use date2, else use null. 

The resulting output in the date field becomes 1/1/1900 instead of blank. In a standard column (non-date type) the expression result will be blank but the date type field is not behaving the same. 

Thanks for any tips/advice you can give me. 

Try something like this. I haven't tested it, but this comes to mind based on what you stated.  If you want a blank, you will need to change the date value to a character string:

COALESCE( CAST(date1 AS VARCHAR(10)), CAST(date2 AS VARCHAR(10)), '' )

or 

COALESCE( TO_CHAR(date1, 'MM/DD/YYYY'), TO_CHAR(date2, 'MM/DD/YYYY'), '' )

Let me know if that works for you or not.

I just tested COALESCE( CAST(date1 AS VARCHAR(10)), CAST(date2 AS VARCHAR(10)), '' ) and that does work.

Hi Daniel, Thank you so much for getting back to me so quickly on this. I've passed this information along to our user. 

One more question, is the expression character that way or date?

Thanks so much for sharing your knowledge on this. 

If you leave the expression type as a character, the display is YYYY-MM-DD.  If you change the expression type as date then it will display as MM/DD/YYYY.

Ours is not SQL Server, but does show a blank when null. I'll show screenshots in case it helps.

As yours is SQL Server, you may need to do something with the NULL e.g.

Cast(null AS datetime)

------------------------------
Daron Wild
Senior PeopleSoft Developer
University of Cambridge
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hi Daron, thank you so much! I've passed this along to our user. 

Thank you for the quick response and the detailed information. Very much appreciated. 

Cast(null AS datetime)