## 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.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
• 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