Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am currently loading a bunch of our system-generated reports (.txt files) into QV to do my own analysis.
We are going to start sending Birthday SMS's to all our customers - until we developed this functionality in the system I will be manually extracting the Birthday Lists every morning.
In our Account Extract we have a [Date of Birth] field in this format: DD-MMM-YY (e.g. 01-MAR-85). In some extracts the dates also have timestamps, so:
I import this in the following way:
DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) as [Date of birth],
This then gives me all dates as YYYY/MM/DD.
What I want to do is load a new field (derived from Date of Birth) called Birthday - the field should be equal to the Date and Month and the Current Year, e.g. if DOB is 1985/03/01 then Birthday must be 2012/03/01.
I can't figure this out - any ideas?
Thanks,
Gerhard
Hi,
Have you tried with this?
MakeDate(Year(Today()),Month([Date of birth]),Day([Date of birth])) as Birthday
or
MakeDate(Year(Today()),Month(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) ),Day(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) )) as Birthday
Hope it helps
Celambarasan
Hi,
Have you tried with this?
MakeDate(Year(Today()),Month([Date of birth]),Day([Date of birth])) as Birthday
or
MakeDate(Year(Today()),Month(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) ),Day(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) )) as Birthday
Hope it helps
Celambarasan
Thanks!
Second option worked perfectly.
Hi,
I think [Date of birth] field doesn't have a proper date format is it so?
Celambarasan
Hi,
Do you mean in the original extract I pull in? No - it is DD-MMM-YY.
But once I pull it into QV using DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) as [Date of birth],
it does.
G