Date coversion

[psandhya408]

Hi,

I have a date1 in the format DDMMYYYY and date 2 in the format Mon DD,YYYY 12:00:00 AM

I need a difference between the two dates in years

I am trying to use _years_between function to find the difference

How to convert DDMMYYY to Mon DD,YYYY format

Please help guys .

[Lynn]

What are the actual data types of those two elements? Perhaps you need to use the cast function to get them to a date data type in order to use any date functions. Do you get an error when you try _years_between? Sharing any error messages might help improve responses from people here on the forum.

[raj_aries]

Hi Sandy,

What is your DB, SQL Server or Oracle?

Regards
Raj

[psandhya408]

we have Oracle

[psandhya408]

What are the actual data types of those two elements? Perhaps you need to use the cast function to get them to a date data type in order to use any date functions. Do you get an error when you try _years_between? Sharing any error messages might help improve responses from people here on the forum.

date1 is varchar2 data type
date 2 is date data type

i converted date1 to YYYY-MM-DD  and calculated

Age = _years_between( Modified date1,cast(date2,date))
but I am facing the issue

[RubenvdLinden]

I don't think you need to cast date2 since it's a date already.

How did you convert the varchar? In Oracle, you can use to_date(date1; 'ddmmyyyy').
Another solution would be: cast(substring(date1;5;4) + '-' + substring(date1;3;2) + '-' + substring(date1;1;2); date)

[Lynn]

You say "I am facing the issue" without any explanation of what the issue is!!!!!!!!!!!

What exactly is the issue? Are you getting some error message? If so, then how about posting it so people don't have to guess. If you aren't getting an error message than what is the problem? Is the result not coming back as you expect? If so, then how about posting some examples so people don't have to guess.

How exactly did you convert your varchar date1 to YYYY-MM-DD? Simply displaying something in a particular format does not make it a date data type. Can you share the expression you used to do that conversion?

[yogeswari]

Hi Sandy,

Your question is not clear.
You can change date format by using format property
If u use Oracle then try cast function, to_date() function

[psandhya408]

You say "I am facing the issue" without any explanation of what the issue is!!!!!!!!!!!

What exactly is the issue? Are you getting some error message? If so, then how about posting it so people don't have to guess. If you aren't getting an error message than what is the problem? Is the result not coming back as you expect? If so, then how about posting some examples so people don't have to guess.

How exactly did you convert your varchar date1 to YYYY-MM-DD? Simply displaying something in a particular format does not make it a date data type. Can you share the expression you used to do that conversion?

I am sorry for not being clear about my question

I will explain it again.. I have two date columns birthdate and reportingdate . I need the years between these two dates

Birthdate is of type date ( Eg: June 8,2000)
ReportingDate is of type Varchar2 (26102012)(DDMMYYYY)

Intially I converted reportingdate to YYYY-MM-DD in this way

NewReporting Date=substring(reportingdate,5,4)+'-'+substring(reportingdate,1,2)+'-'+substring(reportingdate,3,2) -this was working fine

I used to _years_between(birthdate,Modified Reporting date) to find the difference . I got the below error.

The operation "_years_between" is invalid for the following combination of data types: "date2" and "varchar"

As I got this error I thought of using cast function to convert Modified Reporting date to date type

cast('Modified Reporting date',date)

I got the below error  ORA-01841: Full year must be between -4713 and +9999 and not be 0.

I have some null values in the reportingdate column

My backend database is Oracle

Please suggest. Thanks.

[calson33]

Use
to_date(reportingdate,'DDMMYYYY') to convert your date.

[psandhya408]

Use
to_date(reportingdate,'DDMMYYYY') to convert your date.

'


Tried that one too..getting this error.

ORA-01843: not a valid month

[calson33]

'


Tried that one too..getting this error.

ORA-01843: not a valid month

Sounds like you have some bad data then, or maybe you need to prefix some of the data with a zero. for example:
1112012
That could be 11/1/2012 or 1/11/2012 to the converter. It is looking for 01112012

I would say the first step is to check the length of each of those fields:

Select * from your_table where length(reportingdate) < 6

If nothing shows up there, you will have to check the month portion of the text. Break it out, convert it to a number and check that it is less than or equal to 12.

[yogeswari]

Hi,

I have taken one value which is of "date" data type.
Then i have taken value which is of string i.e., varchar data type.

Now i have converted them into "date" by using to_char function.  Eg: cast(to_date(26102012,'DDMMYYYY') as DATE)

The difference between two value:
_years_between ([Date (ship date)],[Data Item1])

Hence the result.
Check and tell what you get sandy :).

Thanks,
Yogeswari.

[yogeswari]

by mistake,

used "to_date" function .

[psandhya408]

Sounds like you have some bad data then, or maybe you need to prefix some of the data with a zero. for example:
1112012
That could be 11/1/2012 or 1/11/2012 to the converter. It is looking for 01112012

I would say the first step is to check the length of each of those fields:

Select * from your_table where length(reportingdate) < 6

If nothing shows up there, you will have to check the month portion of the text. Break it out, convert it to a number and check that it is less than or equal to 12.

Thanks Calson..

I will check the length and will see the data

[psandhya408]

Hi,

I have taken one value which is of "date" data type.
Then i have taken value which is of string i.e., varchar data type.

Now i have converted them into "date" by using to_char function.  Eg: cast(to_date(26102012,'DDMMYYYY') as DATE)

The difference between two value:
_years_between ([Date (ship date)],[Data Item1])

Hence the result.
Check and tell what you get sandy :).

Thanks,
Yogeswari.

Will try this and update
Thanks for ur help :)

[psandhya408]

Sounds like you have some bad data then, or maybe you need to prefix some of the data with a zero. for example:
1112012
That could be 11/1/2012 or 1/11/2012 to the converter. It is looking for 01112012

I would say the first step is to check the length of each of those fields:

Select * from your_table where length(reportingdate) < 6

If nothing shows up there, you will have to check the month portion of the text. Break it out, convert it to a number and check that it is less than or equal to 12.

I checked the data in the tables and I dont see any data without less than length 8.
and also the values in month are lless than or equal to 12

[psandhya408]

Hi,

I have taken one value which is of "date" data type.
Then i have taken value which is of string i.e., varchar data type.

Now i have converted them into "date" by using to_char function.  Eg: cast(to_date(26102012,'DDMMYYYY') as DATE)

The difference between two value:
_years_between ([Date (ship date)],[Data Item1])

Hence the result.
Check and tell what you get sandy :).

Thanks,
Yogeswari.

I converted string to date using the cast function which u have told. It is working fine in Toad
But there is an issue in cognos report studio.

ORA-01843: not a valid month .

I checked the data and the data is correct. I am not sure what is wrong in cognos.

[Lynn]

The ORA error indicates it is a database error. I suspect you have bad data as was earlier suggested by calson33 . The error won't occur until you attempt to query the record that has the bad data. In Toad where you only go through a few of the records you might be fine, but in Cognos if you run to Excel or PDF or even just a different order in HTML could cause you to encounter the problem you didn't happen to reach in Toad.

I'd suggest as calson33 did earlier....run queries to parse out the month portion of the date in your string column and look for values that are not valid months...e.g. not in ('01', '02', '03'....'12').

[Satheesh]

Sandy There is no problem in Cognos can u tell me which Database your using...???

[MFGF]

Sandy There is no problem in Cognos can u tell me which Database your using...???

The

ORA-01843: not a valid month

error message and the fourth post in the thread

we have Oracle

suggest to me it might just be Oracle. :)

MF.

[Satheesh]

Sandy do u have date dimention.. ??? ??
Can u do one thing...??
send the requirement to my ID satheesh504@gmail.com
I can help you...
If u hv date dimention Is there any Query Item like to give DateTime :)