nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Converting String to Date

User_ZZQJ0

I would like to convert the following string value below to a date.

REPLACE(CAST(MONTH("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(DAY("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(YEAR(CURRENT_DATE) AS CHAR),' ','')

Thanks in advance

Find more posts tagged with

Accepted answers

All comments

Sherry George

Hi,

Could you tell us what your requirement is ? Are you trying to calculate current age or something ? Also please provide sample values for the columns used and their data type.

Gianni Ceresa

+1 Sherry George: @3208479 if you want to convert a string to a date at least you could post the string you talk about ...

Your formula seems to find the date of the birthday in the current year (so if you were born the 15 June 1980 your formula would return 15 June 2017).

And as Sherry George said, what are you trying to do exactly? Because there is probably a simpler/shorter way to get to that point than using a weird formula to generate a string, transform it back in a date and then use it for some calculations.

To transform a varchar into a date there is a formula: TO_DATETIME

SyntaxTO_DATETIME('string1', 'DateTime_formatting_string')Where:string1 is the string literal you want to convertDateTime_formatting_string is the DateTime format you want to use, such asyyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month,dd represents day, hh represents hour, mi represents minutes, and ss representsseconds.

User_ZZQJ0

Hello Sherry,

I am creating a report that will show all the people in a company that have upcoming birthdays in the next 30 days. The idea was to get extract the month and the day from the birthday and then append the year with the current year. I could then do a comparison where MYDATE between CURRENT_DATE and CURRENT_DATE + 30.

The birthday is in the format MM/DD/YYYY. MYDATE should be in the same format but I was having trouble converting the string below into the date format.

REPLACE(CAST(MONTH("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(DAY("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(YEAR(CURRENT_DATE) AS CHAR),' ','')

Sherry George

Thank you for providing the details. Based on a quick try, will something like this work for you. It calculates the number of days between current date and and birthday. You can replace Time.Date with Date of Birth.

TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, TIMESTAMPADD(SQL_TSI_YEAR,CAST( (TIMESTAMPDIFF(SQL_TSI_YEAR,"Time"."Date", CURRENT_DATE)) AS INT), "Time"."Date"))

Gianni Ceresa

Good try but it doesn't manage well the year change.

When you are in December in the next 30 days you must get people born in January and for now they are missing (because it will find the birtday of the current year instead of thinking at the next one).

It's a common issue when having to deal with that kind of things

PS: I would say you do not need to make a CAST on the TIMESTAMPDIFF(SQL_TSI_YEAR ....) : it already return an INT only (timestampdiff will never give you anything else than integer numbers)

Sherry George

Good find, didn't think of that. The CAST, I probably did some messing up of the syntax when I tried without CAST (probably the brackets), anyway CAST is not needed.

Sherry George

To address the concern for year change, I think you could find the difference between current date and next year birthday in a different column and then 'OR' the two columns in the filter. There might be a more elegant solution, but for what it's worth..

User_ZZQJ0

Thanks Sherry & Gianni. I'll try your recommendations and see if that works for me.