## SQL convert numeric to date format

2555 pts.
Tags:
SQL
SQL Date Calculation
How can I convert an 8 digit numeric (format ccyymmdd) into a real datefield to be used in date arithmetics?

I need to calculate the number of months between the month (current date) and a date represented in my DB as an 8 digit numeric with format "ccyymmdd". Therefor (I assume) I need to convert my 8 digit column into a date field to be able to use date arithmetics.

Related Questions

Thanks. We'll let you know when a new response is added.

Dane
I RPG it’s:

```%date(mydt80:*iso0)
but I don't think that SQL has an ISO0
so it's
Date(substr(char(mydt90).1.4) || '-'||
substr(char(mydt90).5.2) || '-'||
substr(char(mydt90).7.2) , ISO)```

Phil

————– kccrosser

Cast and Convert allow converting to/from datetime and strings easily, and supports multiple different ISO and non-ISO formats.

Convert(datetime, <inputvar>, 114) will convert a string of the form ccyymmdd to a datetime value.

SQL Books Online has all the codes used to convert back and forth.

Then, once you have two dates, the months between them is easily obtained with DateDiff and GetDate:

```declare @datestring varchar(8)
declare @datethen datetime
declare @monthssince int
```

```set @datestring = '20060724'
set @datethen = convert(datetime, @datestring, 114)
set @monthssince = datediff(m, @datethen, getdate())```

——————————–
Sorry, finally got to test this.
Had a couple of periods and didn’t need ISO

```Date(substr(char(mydt80),1,4) || '-'||
substr(char(mydt80),5,2) || '-'||
substr(char(mydt80),7,2) )

Select Fld1, Fld2,
Date(substr(char(mydt80),1,4) || '-'||
substr(char(mydt80),5,2) || '-'||
substr(char(mydt80),7,2) )```

from myfile

Phil

## Discuss This Question: 8 Replies

Thanks. We'll let you know when a new response is added.
• Phil, As far as I can see, your code leaves me with a char field; - what is need is a date-field that can be used in dta-calcs. Thanks for your interest. Dan
report
• What database system do you need to do this on ?
report
• hi Carlosdl, I'm using an IBM Power 6 System i, so opsys is OS/400 Dan
report
• hi Carlosdl, ... to be more precise: DB2. Dan
report
• You were doing SQL with a numeric field mydt80 you 1. convert it to char -- char(mydt80) 2. get substrings to build ccyy-mm-dd 3. do a date(isodatestring,ISO) to get a date. Sorry, don't have a computer today. Date(substr(char(mydt80).1.4) || '-'|| substr(char(mydt80).5.2) || '-'|| substr(char(mydt80).7.2) , ISO) Phil
report
• convert(datetime, @datestring, 114) is great but I don't think it's available in AS/400 SQL Phil
report
• Check the following link for discussions on date/time conversions in DB2. Unfortunately, it doesn't have an equivalent to the SQL Convert/Cast functions for date/time, so you will need to write one. The page at the link discusses these in detail and gives some example functions. http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html