CODEWITHSUNDEEP
c#db2ibm-midrangedb2-400
Darin
Darin

Reputation: 47

SQL correctly format integers as dates

I am writing a program in C# and a part of it is to grab dates from a DB2 server. The dates are stored as length 4 integer values on the server. The date holds only month and day The problem I have is that they are stored with different accuracy. EDIT: The data type is numeric length 4 with no precision(So an integer length 4) but when the Select statement runs everything is returned as a string. Which is why I was using SubStr().

Example

One date is stored as 1003 representing the date 10/03

Another date is stored as 805 representing 8/05

The SQL code I use for pulling the dates

(SubStr(ML2DDM,0,3) ||'/'|| SubStr(ML2DDM,3,2))as Due__Date

The program returns the dates in the following format

10/03

80/5 <<<< Thats the problem

Is there a way to format the values correctly every time?

Upvotes: 1

Views: 117

Answers (6)

CRPence
CRPence

Reputation: 1259

Given that, like so many posts, there is no DDL given, I can offer that:

Irrespective the DDL for ML2DDM being CHAR(4), VARCHAR(4), SMALLINT, DECIMAL, NUMERIC, BIGINT, or INTEGER [of the numeric types, then also ensured of no more than four digits of precision for any actual values, though the actual column precision is immaterial, but only if the column also is defined with zero-scale; which of course, is implied by the various INT types], the following expression should suffice to insert a / character between the MM and DD components of each MMDD datum that was stored with either a value of three digits ### or a value of four digits #### [where # represents any decimal digit zero through nine]; if the digits are stored as character strings, then they must be stored left-justified, and any non-digit data will be similarly edited, despite they are not valid representation of actual date-values, but neither would the edited result of the numeric values be ensured to be valid in that way:

   insert ( case length(rtrim(ML2DDM))  
             when 3 then '0' else '' end
            concat ML2DDM               
          , 3, 0, '/' ) as due__date_ins

Knowing the actual DDL could allow for a more succinct expression.

If an actual DATE data type result is desirable, an expression such as the one offered above could replace the expr in the following variation of a TIMESTAMP_FORMAT expression, to generate a TIMESTAMP with the current-year value TO_DATE( expr, 'MMDD' ), and that could be cast to DATE; e.g. DATE( TO_DATE( expr, 'MMDD' ) ) Of course the results of this additional casting would require absolutely that the data values be stored as legitimate MMDD [or MDD] values.

30-Sep-2016 Addendum:
If the DDL for ML2DDM is numeric [non-float], then IIRC the implicit cast effect from numeric to character is casting to VARCHAR, so the following would be a quite simple expression to effect both MMDD to "MM/DD" and MDD to "MM/DD":

 insert ( LPAD( ML2DDM , 4, '0' ) , 3, 0, '/' ) as Due__Date

Upvotes: 0

Esperento57
Esperento57

Reputation: 17472

 select left(right(repeat('0', 4) || trim(ML2DDM), 4), 2) || '/' ||  
 right(right(repeat('0', 4) || trim(ML2DDM), 4), 2)  as Due__Date

Upvotes: 1

danny117
danny117

Reputation: 5651

How to get bad data readable. I use this technique mostly for making opening and closing tags or adding line feeds when outputing html, xml, plain text. All there ways.

select 
case when length(trim(ml2ddm)) = 4 
then substring(ml2ddm,1,2) || '/' || substring(ml2ddm,3)
when substring(trim(ml2ddm,4,1) = ' ' 
then substring(ml2ddm,1,1) || '/' || substring(ml2ddm,2) 
else substring(ml2ddm,2,1) || '/' || substring(ml2ddm,3)  
end as somefunkydate
from somereallyfunkydata

Upvotes: 0

Charles
Charles

Reputation: 23803

Apparently you have integers stored as character. As you wouldn't be able to use SUBSTR() on an integer column.

Couple of SQL solutions

Convert to zoned/packed decimal then used DIGITS() to convert back to character and include lead zeros

select 
  (SubStr(digits(dec(ML2DDM,4)),1,2) 
    ||'/'|| SubStr(digits(dec(ML2DDM,4)),3,2))as Due__Date

Add some lead zeros, then take the RIGHT() most characters...

select 
  (left(right(trim('0' || ML2DDM),4),2)
    ||'/'|| right(trim('0'|| ML2DDM),2) as Due__Date

The TRIM() is needed if the column is fixed length character instead of VARCHAR.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063328

If they are integers, it seems that doing string functions is a bad idea. I would just pull the integer back "as is", and deal with it in C#, then you have:

int month = val / 100;
int day = val % 100;

Which you can thendo whatever you need to with. I expect that your RDBMS also has integer division and modulo arithmetic built in, so you can probably do it at the server too. For example, in SQL Server (because I don't know DB2):

declare @v int = 805; -- obviously in real code this would be a column
select @v / 100 as [month], @v % 100 as [day]

Upvotes: 1

nsevens
nsevens

Reputation: 2835

In C# i'd do something like this:

var fullValue = ML2DDM;
var formattedDate = fullValue.Substring(0, fullValue.Length - 2) + "/" + fullValue.Substring(fullValue.Length - 2, 2);

I don't know any DB2, but perhaps the same approach works? Something like:

(SubStr(ML2DDM,0,length(ML2DDM)-2) ||'/'|| SubStr(ML2DDM,length(ML2DDM)-2,2))as Due__Date

Upvotes: 0

Related Questions