SQL convert numeric to date format

2555 pts.
Tags:
SQL
SQL Date Calculation
SQL datetime
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.

 

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • DanTheDane
    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
    2,555 pointsBadges:
    report
  • carlosdl
    What database system do you need to do this on ?
    68,585 pointsBadges:
    report
  • DanTheDane
    hi Carlosdl, I'm using an IBM Power 6 System i, so opsys is OS/400 Dan
    2,555 pointsBadges:
    report
  • DanTheDane
    hi Carlosdl, ... to be more precise: DB2. Dan
    2,555 pointsBadges:
    report
  • philpl1jb
    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
    49,590 pointsBadges:
    report
  • philpl1jb
    convert(datetime, @datestring, 114) is great but I don't think it's available in AS/400 SQL Phil
    49,590 pointsBadges:
    report
  • Kccrosser
    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
    3,830 pointsBadges:
    report
  • Kccrosser
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following