469,626 Members | 880 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Convert Null in Date field to String

Hi,

My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.

Nov 20 '06 #1
10 37287
SELECT COALESCE(VARCHAR(datefield), '') FROM X...

sa**********@gmail.com wrote:
Hi,

My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.
Nov 20 '06 #2
Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.

Have a good day !
jefftyzzer wrote:
SELECT COALESCE(VARCHAR(datefield), '') FROM X...

sa**********@gmail.com wrote:
Hi,

My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.
Nov 20 '06 #3
My pleasure. Glad it worked!

--Jeff

sa**********@gmail.com wrote:
Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.

Have a good day !
jefftyzzer wrote:
SELECT COALESCE(VARCHAR(datefield), '') FROM X...

sa**********@gmail.com wrote:
Hi,
>
My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.
Nov 20 '06 #4
This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.

I don't suppose that there's anything similar going the opposite direction.
In other words,

MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.

Thoughts?

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>jefftyzzer<je********@sbcglobal.net11/20/06 12:50 PM >>>
My pleasure. Glad it worked!

--Jeff

sa**********@gmail.com wrote:
Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.

Have a good day !
jefftyzzer wrote:
SELECT COALESCE(VARCHAR(datefield), '') FROM X...

sa**********@gmail.com wrote:
Hi,
>
My SQL returns a NULL in a datefield if there is no date in that
field.
If there is a NULL in this column, I want to replace it with spaces
in
my SELECT statement when I am selecting these records. Can any of
you
experts out there help me with a sample SQL.


Nov 23 '06 #5
You might try:
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (CASE WHEN :NULLABLE-CHAR-FIELD = ''
THEN NULL
ELSE :NULLABLE-CHAR-FIELD
)

Phil Sherman

Frank Swarbrick wrote:
This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.

I don't suppose that there's anything similar going the opposite direction.
In other words,

MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.

Thoughts?

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>>jefftyzzer<je********@sbcglobal.net11/20/06 12:50 PM >>>
My pleasure. Glad it worked!

--Jeff

sa**********@gmail.com wrote:
>Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.

Have a good day !
jefftyzzer wrote:
>>SELECT COALESCE(VARCHAR(datefield), '') FROM X...

sa**********@gmail.com wrote:
Hi,

My SQL returns a NULL in a datefield if there is no date in that
field.
>>>If there is a NULL in this column, I want to replace it with spaces
in
>>>my SELECT statement when I am selecting these records. Can any of
you
>>>experts out there help me with a sample SQL.


Nov 23 '06 #6

Frank Swarbrick wrote:
>
MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.
Can you use NULLIF? Like this.
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULLIF(:NULLABLE-CHAR-FIELD,''))
END-EXEC

Nov 23 '06 #7
Frank Swarbrick wrote:
This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.
The main thing to recognize here is that the result is a string and not a
date value. An empty string '' is not a valid date in the first place.
I don't suppose that there's anything similar going the opposite
direction. In other words,

MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple,
I think.
You can use the built-in NULLIF function for that.

If you have a string consisting of whitespaces only and you want to have
this string interpreted as NULL date, then a CASE expression is needed:

CASE
WHEN TRANSLATE(str, '', ' ') = ''
THEN NULL
ELSE str
END

If it is permissable to remove whitespaces from the string even if the
string is not empty (and should not be stored as NULL), you can nest
TRANSLATE into NULLIF:

NULLIF(TRANSLATE(str, '', ' '), '')

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 23 '06 #8
Tonkuma<to*****@jp.ibm.com11/22/06 11:17 PM >>>
>
Frank Swarbrick wrote:
>>
MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which
case
>it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly
simple, I
>think.
Can you use NULLIF? Like this.
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULLIF(:NULLABLE-CHAR-FIELD,''))
END-EXEC
That is perfect! Thank you!

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Nov 24 '06 #9
Knut Stolze<st****@de.ibm.com11/23/06 1:00 AM >>>
>Frank Swarbrick wrote:
>This is pretty darn cool! Much nicer than using "indicator variables"
if
>you don't really care that it's a NULL field.

The main thing to recognize here is that the result is a string and not a
date value. An empty string '' is not a valid date in the first place.
Yes, but within a COBOL program that seems to me to be perfectly
acceptable.
>I don't suppose that there's anything similar going the opposite
direction. In other words,

MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which
case
>it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly
simple,
>I think.

You can use the built-in NULLIF function for that.

If you have a string consisting of whitespaces only and you want to have
this string interpreted as NULL date, then a CASE expression is needed:

CASE
WHEN TRANSLATE(str, '', ' ') = ''
THEN NULL
ELSE str
END

If it is permissable to remove whitespaces from the string even if the
string is not empty (and should not be stored as NULL), you can nest
TRANSLATE into NULLIF:

NULLIF(TRANSLATE(str, '', ' '), '')
I'm not sure I understand why either of these is necessary. I just did the
following:
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100) );
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 SQL-FIELDS.
05 TIMESTAMP1 PIC X(26).
05 NULL1VC PIC X(100).
EXEC SQL
END DECLARE SECTION
END-EXEC

PROCEDURE DIVISION.
EXEC SQL
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100) );
END-EXEC

MOVE '2006-11-24-11.58.53.012345'
TO TIMESTAMP1
MOVE 'This is a test' TO NULL1VC
PERFORM INSERT-TEST2
MOVE SPACES TO TIMESTAMP1, NULL1VC
PERFORM INSERT-TEST2
Nov 24 '06 #10
Frank Swarbrick wrote:
>>You can use the built-in NULLIF function for that.

If you have a string consisting of whitespaces only and you want to have
this string interpreted as NULL date, then a CASE expression is needed:

CASE
WHEN TRANSLATE(str, '', ' ') = ''
THEN NULL
ELSE str
END

If it is permissable to remove whitespaces from the string even if the
string is not empty (and should not be stored as NULL), you can nest
TRANSLATE into NULLIF:

NULLIF(TRANSLATE(str, '', ' '), '')

I'm not sure I understand why either of these is necessary. I just did
the following:
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100) );
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 SQL-FIELDS.
05 TIMESTAMP1 PIC X(26).
05 NULL1VC PIC X(100).
EXEC SQL
END DECLARE SECTION
END-EXEC

PROCEDURE DIVISION.
EXEC SQL
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100)
);
END-EXEC

MOVE '2006-11-24-11.58.53.012345'
TO TIMESTAMP1
MOVE 'This is a test' TO NULL1VC
PERFORM INSERT-TEST2
MOVE SPACES TO TIMESTAMP1, NULL1VC
PERFORM INSERT-TEST2
.

INSERT-TEST2.
EXEC SQL
INSERT INTO TEST2 (
TIMESTAMP1,
NULL1
)
VALUES (
NULLIF(:TIMESTAMP1,''),
NULLIF(RTRIM(:NULL1VC),'')
)
END-EXEC
.

I ended up with two rows, the first having the timestamp and varchar field
set appropriately, and the second having both set to NULL. Seems right to
me.
You should verify your results. If I read the above code correctly and
translate it to pure SQL, then this would be equivalent:

CREATE TABLE test2( t timestamp, n varchar(100) );
INSERT INTO test2
VALUES ( NULLIF('2006-11-24-11.58.53.012345', ''),
NULLIF(RTRIM('This is a test'), '') );
INSERT INTO test2
VALUES ( NULLIF('2006-11-24-11.58.53.012345', ''),
NULLIF(RTRIM(' '), '') );

The result of the first insert cannot have NULL in the second column because
the strim 'This is a test' is not empty, even if trimmed:

SELECT * FROM test2

T N
-------------------------- ---------------------------
2006-11-24-11.58.53.012345 This is a test
2006-11-24-11.58.53.012345 -

Anyway, my whole point about using TRANSLATE (RTRIM is equally fine) for
converting from string to timestamp is that a string like ' ' is _not_ a
valid timestamp. You have to remove the spaces so that NULLIF will truly
get an empty string and convert that to NULL.

VALUES timestamp(' ')
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007
create table test2( t timestamp, n varchar(100) )
While we're on the subject of date/time stuff, is there a function that
takes in, say, a DECIMAL(8) or DECIMAL(9) field set to something like
20061103 and translates it into a DB2 DATE field? I see there's a DATE()
function, but that doesn't quite do what I'm looking for, as far as I can
tell. Of course it's simple enough to fiddle around using a separate
COBOL working-storage field to get it to work, but it would be nice if I
didn't have to.
Maybe the TO_DATE function works for you?

Other than that, you could apply some calculations and stick with the DATE
function:

CREATE FUNCTION getDate(d INT)
RETURNS DATE
RETURN DATE('0001-01-01') +
((d / 10000) - 1) YEARS +
(MOD(d / 100, 100) - 1) MONTHS +
(MOD(d, 100) - 1) DAYS;

VALUES getDate(20061127)

1
----------
11/27/2006

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 27 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Paulb1us | last post: by
6 posts views Thread by Dean Slindee | last post: by
12 posts views Thread by Brian Henry | last post: by
7 posts views Thread by Mike Howard | last post: by
3 posts views Thread by Elliot | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.