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.

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. 

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

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.

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. 

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. 

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

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.

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. 

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. 

------------------------------
Lisa Romero
Sr PS Financials Application Systems Analyst
Salt River Pima Maricopa Indian Community
------------------------------

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

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.

Original Message:
Sent: 02-06-2025 06:17 PM
From: Lisa Romero
Subject: Show nothing in Date field when expression result is null

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. 

------------------------------
Lisa Romero
Sr PS Financials Application Systems Analyst
Salt River Pima Maricopa Indian Community
------------------------------

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