Date

[smilingjohn]

HI All ,

How to convert dte into age ,

Iam attaching a sample data of xl and QVW file , i want to create one more field by name AGE using the dat field .Can someone help me please

Thanks in Advance....

[tamilarasu]

John,

Simply try,

  Age(Date(Today()),Date(Floor(COLLDATEODBC))) as Age

LOAD ACCNUMBER,
     BATCODE,
     BIRTHDATEODBC,
     COLLDATEODBC,
     Age(Date(Today()),Date(Floor(COLLDATEODBC))) AS Age,
     COLLTIMEODBC,
     DIREXAM,
     DRUGCODE,
     DRUGNAME,
     PTNAME,
     DRUGRESULT,
     FINALDATEODBC,
     LOCNAME,
     LOCTYPE,
     MO_GROUPCODE,
     ORDERPHYS,
     ORGANISMCODE,
     ORGCODE,
     ORGDUPFLAG    
FROM

(ooxml, embedded labels, table is Data);

[senpradip007]

Try like

Age(Today(), TimeStamp(Timestamp#(COLLDATEODBC, 'M/D/YYYY  hh:mm:ssTT'))) AS Age,

[tresesco]

It is working. Only thing is - you have to understand is Age() -  Returns the age at the time of timestamp (in completed years). That means if not a complete year it would return 0 and that is the case in your sample qvw. If you want your age calculation to include month, day difference - age() would not help, you might try with interval() instead.

One more thing - your expression could be optimized a little by removing the unnecessary timestamp(), like:

Age(Now(), TimeStamp(Timestamp#(COLLDATEODBC, 'M/D/YYYY  hh:mm:ssTT')))

Or, even usign parsing function(since, it seems your source data is a proper numeric date field)

Age(Now(), COLLDATEODBC) as Age

[#5]

Hi

Please try this

Age:

LOAD AGE,

subfield(AGE,' ',1) as Date,

SubField(AGE,' ',2) as Time

FROM

(ooxml, embedded labels, table is Sheet1);

Data:

load

*,

AGE(today(),Date) as Totalage

resident Age;

DROP Table Age;

[smilingjohn]

HI Ishfaque,

PLease find the attachment its not working

[tresesco]

May be like Stefan suggested here: Age in Year Month Day Format

[smilingjohn]

Hi jagan after following your steps i loaded but its showing 0 in age plz find he snapshot

[smilingjohn]

hi Nagraj

Same again every where its shoing 0 only  as shown in the screen shot

[jagan]

Hi,

Try this

LOAD ACCNUMBER,

     BATCODE,

     BIRTHDATEODBC,

     COLLDATEODBC,    

     Age(Now(), Date(Date#(SubField(COLLDATEODBC, ' '), 'M/D/YYYY'))) AS Age,

     COLLTIMEODBC,

     DIREXAM,

     DRUGCODE,

     DRUGNAME,

     PTNAME,

     DRUGRESULT,

     FINALDATEODBC,

     LOCNAME,

     LOCTYPE,

     MO_GROUPCODE,

     ORDERPHYS,

     ORGANISMCODE,

     ORGCODE,

     ORGDUPFLAG

FROM

(ooxml, embedded labels, table is Data);

[tamilarasu]

Age function shows only years. Actually, the year is 0 in your case, since your date values are 8/1/2015  12:00:00AM and 8/2/2015  12:00:00AM (Not yet completed one year). Could you load full data and check the result.!