Birthday field

[gerhardl]

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

[CELAMBARASAN · Accepted solution]

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

[gerhardl]

Thanks!

Second option worked perfectly.

[CELAMBARASAN]

Hi,

     I think [Date of birth] field doesn't have a proper date format is it so?

Celambarasan

[gerhardl]

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