Funny SQL date function

[DeadManWalks]

So i have this;

select     strnum,                                                
                 Case When a.stsdat <> 0 then                     
                 Cast (                                           
                 VarChar_Format(Timestamp_Format(Char(            
                 ((a.stscen + 19 ) * 1000000 ) + a.stsdat),       
                  'YYYYMMDD'), 'YYYY-MM-DD')                      
                  as Date)                                        
                  else                                            
                  Cast ('0001-01-01' as date) end "Date Opened"   
                                                                  
from tblstr  a

I get null for fields with a zero in the stsdat.

If i use

select     strnum,                                               
                 Case When a.stsdat <> 0 then                    
                 Cast (                                          
                 VarChar_Format(Timestamp_Format(Char(           
                 ((a.stscen + 19 ) * 1000000 ) + a.stsdat),      
                  'YYYYMMDD'), 'YYYY-MM-DD')                     
                  as Date)                                       
                  else                                           
                  Cast ('1940-01-01' as date) end "Date Opened"  
                                                                 
from tblstr  a

Then i at least get 1940.

any thoughts. My field i am dumping into is not null capable.

[DeadManWalks]

Re: Funny SQL date function

as a side note i can not use dates before 1940 as my default.

[B.Hauser]

Re: Funny SQL date function

The following expression retuns an integer value (10 digits):
(a.stscen + 19 ) * 1000000 ) + a.stsdat)

You need to cast it as Dec(8, 0).
.... and use DIGITS instead of CHAR when converting the numeric value into a character representation.

Birgitta

[TedHolt]

Re: Funny SQL date function

I get null for fields with a zero in the stsdat. . . any thoughts. My field i am dumping into is not null capable.

Here are two more ideas, Dead Man.

(1) Use COALESCE to assign a non-null value to the field. Your expression will be the first parm of COALESCE. The second parm will be the value you want to replace null values.

(2) Use CASE to check for zero in STSDAT. If non-zero, return your expression, else return your null-value replacement.

[Rocky]

Re: Funny SQL date function

You're getting a NULL on the first one probably because you're using *MDY for your date format - 0001-01-01 is outside of the range of MMDDYY is from1940 to 2039 if memory serves.. at any rate it's not a valid date for *MDY format - which is why the second one works as 1940 is an acceptable date.

[B.Hauser]

Re: Funny SQL date function

A date is always stored as scaliger no which is a 4 digit running number.
Date formats are only used to make this scaliger no readable.
SQL can detect, interprete and convert a character representation of a date in one of following formats correctly, independent of the date format used within the current job:
'YYYY-MM-DD', 'MM/DD/YYYY', 'DD.MM.YYYY'.

BTW there are easier ways to convert a numeric century and a numeric 6 digit date into a real date here 2 examples (but not both of them are casting the calulated numeric date into DEC(8, 0) format, otherwise it will NOT work!):

Select Date(Digits(Cast(((stscen + 19 ) * 1000000  + stsdat) as Dec(8, 0))) concat '000000'), 
       Date(Timestamp_Format(Digits(Cast(((stscen + 19 ) * 1000000  + stsdat) as Dec(8, 0))), 'YYYYMMDD')),
from YourTable a

Birgitta

[Rocky]

Re: Funny SQL date function

A date is always stored as scaliger no which is a 4 digit running number.
Date formats are only used to make this scaliger no readable.
SQL can detect, interprete and convert a character representation of a date in one of following formats correctly, independent of the date format used within the current job:
'YYYY-MM-DD', 'MM/DD/YYYY', 'DD.MM.YYYY'.

BTW there are easier ways to convert a numeric century and a numeric 6 digit date into a real date here 2 examples (but not both of them are casting the calulated numeric date into DEC(8, 0) format, otherwise it will NOT work!):

Select Date(Digits(Cast(((stscen + 19 ) * 1000000  + stsdat) as Dec(8, 0))) concat '000000'), 
       Date(Timestamp_Format(Digits(Cast(((stscen + 19 ) * 1000000  + stsdat) as Dec(8, 0))), 'YYYYMMDD')),
from YourTable a

Birgitta

Ok - what does it return when the date format that you want is *MDY - and the date has '0001-01-01'? It's an invalid date - it's valid in *ISO... it's valid in *USA - but it isn't *MDY... hence the problem. Check it out. The issue isn't how a date is stored - it's how it's used.... if your environment is *MDY it will fail for dates before 1940 - each and every time. That is why it the first one with Cast ('0001-01-01' as date) failed while the one with Cast ('1940-01-01' as date) did not.

[B.Hauser]

Re: Funny SQL date function

The return value of an date is always the scaliger no. In SQL is the job's/connection's date format taken for making the date format readable.
If it is an date value outside the valid range, it is simply not displayed, i.e. displayed with whatever (++++++) place holder.
Just try it by yourself.
Create a table with SQL, including a date column. you'll find there is NO date format that can be specified.
Then change your job/connection format
(STRSQL --> F1 --> 1. Change session attributes --> DatFMT or IBM i Navigator / ACS run SQL script --> Connection --> JDBC settings --> Format --> Date)

Insert dates inside and/or outside the valid date range. You'll find all dates are inserted, but the dates outside the visible range are not displayed.
If you change the date format to any 4-digit year format, and display the content of your table, you'll find the dates in the current format and even the dates formerly inserted outside the numeric range are displayed correctly.

You can also check the Hex-Value of a date, it is not readable: Hex(Current_Date) is x'00257F8F' (Today is April 8th 2016)

If you find your embedded SQL fails, this is not an SQL but an RPG problem. RPG always converts the scaliger no in an readable format and dates outside the valid range cannot be stored and RPG crashes. In embedded SQL additional date variables are created by the SQL precompiler, but the precompiler does not care about any date format specified within the D or H-Specs, but takes the format from the compile command or an internal SET OPTION statement. Default for this date format is *JOB (which may be *MDY in your case, i.e. has a 2 digit year). Your RPG program will fail as soon as the RPG-date (which may be '0001-01-01') is moved into this additional SQL-Variable.
Easy solution, just specifiy the date format for SQL to *ISO (or any other Format with a 4 digit year).

BTW even though is possible to specify a date format in an DDS defined file, this dateformat is only used for making the scaliger no readable with WRKF.
You may also create an DDS described file with different dates in different formats and run this table throuhg SQL.
You'll find all dates have the same format and if you change the format of the job. It magically shows the dates within this format.

Birgitta