I have decimal fields (DECIMAL(8,0)) that contain dates as 5122019 for may 12th 2019 and 12122020 as December 12th 2020. I’ve tried several ways to convert including DATE(TIMESTAMP_FORMAT(CHAR(decimalField),’MMDDYYYY’)) but they return null. What’s the best way to do this with a decimal 8,0 field when the single digit months don’t contain a leading zero? Answer It works if you use
Tag: db2
IBM DB2 CAST AS VARCHAR versus Python Pandas to_datetime Function
I have the line in a SQL file hitting an IBM DB2 database. For various reasons, I have to convert to VARCHAR, so leaving out the CAST is not an option. The problem is that this casting is choosing a very poor format. The result comes out like this: 2020-06-09-13.15.00.000000. We have the four-digit year with century, month, day of
DB2 z/OS – Get the last 2 digits of year
SELECT SUBSTR(CAST(YEAR(SOMEDATE) AS VARCHAR(4)),2,2) AS “YY” FROM SOMESCHEMA.FOO; Gets me the following error: [Code: -104, SQL State: 42601] ILLEGAL SYMBOL “,2”. SOME …
Select person in a table having a specific value but not having another value for same id
I tried for hours and read many posts but I still can’t figure out how to handle this request: I have a table like this: +——+——+——+ |PERSON|TRTYPE| ID | +——+——+——+ |JERRY | I …
Does DB2 implement multi-table indexes?
I’m afraid this functionality may not exist [yet]. I wanted to use an index that spans multiple tables in DB2. I know Oracle and SQL server implement them (with more or less options) and that …
Efficient way to ignore whitespace in DB2?
I am running queries in a large IBM DB2 database table (let’s call it T) and have found that the cells for column Identifier tend to be padded not just on the margins, but in between as well, as in: ‘ ID1 ID2 ‘. I do not have rights to update this DB, nor would I, given a number of
SQL subquery with aggregate value
Sorry for the vague title, I’m a little lost here. I have two simple aggregate SQL queries, but I struggle to combine them in a functional way (likely through a subquery) in IBM DB2. Main goal is to run COUNT function in INVOICES table – but with the starting date based on a subquery MAX aggregate result. But as noted,
SQL to detect if one column contains another column
I try to query as following: I need to detect if voucher contains REV and give flag on row level. I tried OVER PARTITION but with no success. Any ideas? Answer Use a CASE expression and EXISTS:
display all the 1year back from current data in the table in db2
this is my table. i want to extract records of last 1 year from current date. so my output should be Answer In the WHERE clause set the condition that dob is greater than the current date minus 1 year: If you want all the rows of the current and the previous year: See the demo.
Combine different CTE json objects together
I am having difficulties grasping the concept of combining two CTE json objects together. The data from the two are matched by a field WOID, which is the “workOrderID” in this json. I do not know …