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.
+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
string1 is the string literal you want to convert
DateTime_formatting_string is the DateTime format you want to use, such as
yyyy.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 represents
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),' ','')
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"))
1 person found this helpful
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)
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.
1 person found this helpful
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..
Thanks Sherry & Gianni. I'll try your recommendations and see if that works for me.