SQL: from integer to date

Question

I have an integer which represents a date. eg: 20230530 and I need to convert it to a date format. I tried

SELECT CONVERT(datetime,convert(char(6), DATE)) FROM MY_TABLE

But it outputs an error:

SQL Error [42704]: [SQL0204] CONVERT in *LIBL type *N not found

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [42704]: [SQL0204] CONVERT in *LIBL type *N not found.
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:509)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:440)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:427)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:808)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3092)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:121)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:119)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4454)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] CONVERT in *LIBL type *N not found.
    at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:948)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:745)
    at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1790)
    at com.ibm.as400.access.AS400JDBCStatement.execute(AS400JDBCStatement.java:2096)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)

Eventually I would like to get the age of a person = date of today - birth date

It's imperative you specify which database you are using. Databases DO NOT share the same sql this is especially true with date and time. — Paul Maxwell, May 31 '23 at 12:15
I can see an IBM sql error [SQL0204] I am going to go with you are using DB2. Refer to this answer. https://stackoverflow.com/a/47932883/2043275 — thusith.92, May 31 '23 at 12:28

Paul Maxwell · Answer 1 · 2023-06-01T03:27:31.537

There are a variety of ways to treat that integer as a date or timestamp. There is also an age function (although the result is an integer representing number of years, months and days e.g. 200002 = 20 years, 0 months, 2 days) e.g:

SELECT 
    TO_DATE(date_integer, 'YYYYMMDD')  
  , age( TO_DATE(date_integer, 'YYYYMMDD') )  / 10000 age_in_years
from my_table

1	AGE_IN_YEARS
2003-05-30 00:00:00.000000	20
1976-01-17 00:00:00.000000	47

Note using to_date(20030502,'YYYYMMDD') results in a timestamp, if you want a date value instead then use date(to_date(20030502,'YYYYMMDD'))

The following is an examination of how one might use age:

select
   date(TO_DATE(date_integer, 'YYYYMMDD'))   as my_dt
 , (d.age_yymmdd / 10000)                    as age_years
 , int(left(right(d.age_yymmdd,4),2))        as age_months
 , int(right(d.age_yymmdd,2))                as age_days
, (d.age_yymmdd / 10000) 
  || ':' || left(right(d.age_yymmdd,4),2)
  || ':' || right(d.age_yymmdd,2)            as age_ymd
 , d.age_yymmdd
from my_table t
join table  (
  select age( date(TO_DATE(t.date_integer, 'YYYYMMDD')) ) age_yymmdd
  from my_table
  ) as d on true

MY_DT	AGE_YEARS	AGE_MONTHS	AGE_DAYS	AGE_YMD	AGE_YYMMDD
2003-05-30	20	0	2	20:00:02	200002
1976-01-17	47	4	15	47:04:15	470415
2003-05-30	20	0	2	20:00:02	200002
1976-01-17	47	4	15	47:04:15	470415

fiddle

nb regarding JOIN TABLE seen in the above this is a "substitute" for a lateral join see: https://stackoverflow.com/a/75085066/2067753

score 2 · Accepted Answer · answered May 31 '23 at 13:32

Convert does not exist in DB2 for IBM i, see documentation

You can get a date type from an int using date(to_date(char(myintdate), 'YYYYMMDD')) and calculate the age using int((current date - birth_date) / 10000)

with t1 (myintdate) as (
    values int(19630514)
  )
select date(to_date(char(myintdate), 'YYYYMMDD')) as myDateDate,
       int((current date - date(to_date(char(myintdate), 'YYYYMMDD'))) / 10000) as age
from t1;

MYDATEDATE	AGE
1963-05-14	60

score 0 · Answer 3 · answered May 31 '23 at 12:30

0

In Your SELECT Statement you declared char(6), change that to char(8). Verify the Length of 20230530.

SELECT CONVERT(datetime,convert(char(8), '20230530'))

answered May 31 '23 at 12:30

gskarthikkn

89
4

score 0 · Answer 4 · answered May 31 '23 at 12:41

0

Try:

WITH 
indata(datestring) AS (
  SELECT '20230530' FROM SYSIBM.SYSDUMMY1
)
SELECT
  TO_DATE(datestring,'YYYYMMDD')
FROM indata;

answered May 31 '23 at 12:41

marcothesane

6,192
1
11
21

score 0 · Answer 5 · answered May 31 '23 at 13:02

0

To get a date without a timestamp, use TO_DATE() to convert an integer to a date datatype, then use VARCHAR_FORMAT() to format it as YYYY-MM-DD or any other format:

SELECT VARCHAR_FORMAT(TO_DATE(datetime, 'YYYYMMDD'),'YYYY-MM-DD')
from MY_TABLE

answered May 31 '23 at 13:02

SelVazi

10,028
2
13
29

SQL: from integer to date

5 Answers5