SQL correctly format integers as dates

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?

Answer by Nsevens for SQL correctly format integers as dates

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  

Answer by Marc Gravell for SQL correctly format integers as dates

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]  

Answer by Charles for SQL correctly format integers as dates

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.

Answer by danny117 for SQL correctly format integers as dates

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  

Answer by Esperento57 for SQL correctly format integers as dates

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

Answer by CRPence for SQL correctly format integers as dates

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   

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72