Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Date arithmetic on a CHAR date in DB2

Can you help with a date calculation? I'm on IBM DB2 if it helps (or hinders). I have a record with a date stored...

in a character field, formatted YYYYMMDD. How can I calculate when this date is less than 30 days from today? Please note, I can't (not allowed to) update the database using SQL, I'm only a lowly user.

Well, I certainly sympathize with your position. Of course, we all know that dates should not be stored in character fields, but this does happen, for any number of reasons, and then we have to work with it.

The main thing we need to do is convert the character value to a date value so that we can do date arithmetic on it. One way is to CAST it, another is to use the DATE function. Once we have a date value, we can compare it to CURRENT DATE like this:

```select rowpk, chardatefield
from yourtable
where abs( days( date(chardatefield) )
- days( current date )
) < 30
```

Note that the DATE function, which converts an expression to a date value, requires a "valid string representation of a date." Despite a good half hour of searching on the IBM site, I was unable to find out exactly which string representations are considered valid. I saw a few examples (and they were few, and hard to find) that suggested that 'yyyy-mm-dd' was a valid string representation, but none that showed 'yyyymmdd'. (Note: even though I was frustrated in this instance, I hasten to point out that my impression of IBM's online documentation is otherwise very good.) Since I don't have access to DB2 and cannot test this myself, I'm going to go out on a limb and say that 'yyyymmdd' probably is a valid string representation of a date, so we can simply say DATE(chardatefield) and not worry about using substrings to pull out the year, month, and day in order to stuff dashes between them.

The second part of the solution involves converting dates to a day number using the DAYS function, which generates a result representing the number of days since January 1, 0000, or something. Note that the actual base date doesn't matter, nor do Julian and Gregorian calendar differences, because we are not concerned about the actual number of days, just their difference. So all that matters is that the day numbers are both calculated relative to the same base date.

The final part of the solution hinges on the fact that you did not specify whether you wanted to select dates less than 30 days from today in the past, in the future, or both. Since the subtraction of day numbers can result in positive or negative numbers depending on whether the date is in the past or future, the use of the ABS function selects dates which are within 30 days in either direction.

Addendum April 18, 2003
A kind reader provided a link to the DB2 Universal Database for iSeries SQL Reference manual, a 4.5meg PDF file on the IBM site. Fortunately, I was able to find the HTML manual as well. See String Representations of Datetime Values. YYYYMMDD is not among the valid values, so in the solution above, change DATE(chardatefield) to:

```DATE(SUBSTR(chardatefield,1,4)||'-'||
SUBSTR(chardatefield,5,2)||'-'||
SUBSTR(chardatefield,7,2)
)
```

This was last published in April 2003

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### DMBOK2 eyes evolving data management best practices, challenges

In a Q&A, DAMA president Sue Geuens talks about the second edition of the group's DMBOK reference book and how data management ...

• ### MongoDB Atlas footprint grows bigger with Azure, Google cloud support

MongoDB has expanded cloud coverage for its Atlas hosted database service, with Azure and Google joining an initial AWS-based ...

• ### Look for advances in serving the Hadoop data scientist

To date, the Hadoop data scientist has had to be a superhero. Self-service tools will change that, but not overnight.

• ### Examining the KNIME Analytics Platform for big data analytics

KNIME offers open source data analytics, reporting and integration tools, as well as commercial software that can help build more...

• ### Machine learning platforms comparison: Amazon, Azure, Google, IBM

The platform war over machine learning tools is heating up. Use our features comparison chart to see how four top vendors stack ...

• ### Exploring Oracle Advanced Analytics

Oracle Advanced Analytics and other data analytics tools, including Oracle R Enterprise and Oracle Data Miner, enable business ...

## SearchSAP

• ### SAP BW/4HANA application could streamline finance and planning

SAP Business Planning and Consolidation for BW/4HANA aims to simplify and consolidate enterprise financial planning; Business ...

• ### Choosing SAP HR module complicated by S/4HANA, SuccessFactors options

A SuccessFactors consultant explains the benefits and challenges of adding to or replacing on-premises SAP HCM with the newer ERP...

• ### Move to SAP S/4HANA Cloud improves sales app's security and performance

Emplay, an SAP Startup Focus partner, moved its Sales GPS app to SAP S/4HANA Cloud and saw improvements in performance, ...

## SearchSQLServer

• ### A closer look at Python-SQL Server 2017 integration

Do you know everything you need to take advantage of SQL Server 2017's support of Python?

• ### How much do you really know about SQL Server 2016 licensing?

Should you use Server + CAL licensing for SQL Server 2016 or does Per Core licensing offer more bang for your buck? Read our ...

## TheServerSide

• ### How microservices patterns made Uber's architecture perform better

How did Uber manage to deal with the inevitable Halloween rush? Applying microservices patterns to their architecture played a ...

• ### Effective DevOps hinges on automating a continuous delivery pipeline

Many organizations struggle to bring development and operations together, only to realize that effective DevOps means ...

• ### How cheap eye tracking software changes the app development game

Find out what the advent of low-cost eye tracking software means for the future of both marketing and application development.

## SearchDataCenter

• ### Distributed data centers boost resiliency, but IT hurdles remain

Distributed data center architectures increase IT resiliency compared to traditional single-site models, with networking, data ...

• ### Server firmware security threat, real or not, gets renewed attention

The latest threat to server security could be the firmware. That's the emphasis of at least one server-maker, hoping to help ...

• ### Assess and compare DCIM software options in the market

DCIM software can improve the management and operation of your data center. Choose the right vendor for your company to control ...

## SearchContentManagement

• ### Three BI dashboard best practices you need to know

As companies struggle with BI integration, Microsoft has figured out the magic formula with its Power BI dashboard. Here are the ...

• ### Adobe Document Cloud adds mobile scan, upgrades e-signature software

Adobe rounds out its cloud suite with Adobe Scan, which allows mobile employees to capture documents and images and turn them ...

• ### Five SQL 2017 improvements you need to know about

SQL Server 2017 has made 5 key improvements that can help integrate AI capabilities and analytics into the enterprise more ...

## SearchFinancialApplications

• ### Ceridian uses employee engagement tool from its own joint venture

After forming a new joint venture, LifeWorks, HCM vendor Ceridian uses LifeWorks' employee recognition, engagement and perks ...

• ### Evaluate HR technology to fit your talent management processes

Choose your talent management system wisely or risk irking your employees. Learn the criteria for evaluating software that ...

• ### SaaS HR software dominates as cloud HCM systems proliferate

HCM systems for big employers are increasingly in SaaS form and incorporating a wide range of HR tech beyond core functions, such...

Close