cancel
Showing results for 
Search instead for 
Did you mean: 

days

Former Member
0 Kudos

converting a crystal syntax to Universe Object.

Age Days >>> CurrentDate - {EMPLOYEE.BIRTH_DATE}

Back end is SQL 2008. Please help me out.

I tried using the code : getdate() - EMPLOYEE.BIRTH_DATE in Universe Object, But in the report it shows just date ( example : 04/25/0 , 05/01/00 etc.....)

In back end for Birth_date the data looks like 1988-02-23 00:00:00:000

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

FLOOR(DATEDIFF(dd,EMPLOYEE.BIRTH_DATE,GETDATE())/365)

Former Member
0 Kudos

Is that Parsed Ok??

YES, then go to Properties of that Object. Next tab(name I forgot), you can see Display(to see all LOV's), just check you are getting as you expected or not?

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Can you see the List of Values at Universe level?? How the Date format is??

Can you see Timestamp also here.

Click on date object -- Properties-- Edit/Display

@ Report:

Select the column (mean Date object) and go to Properties, Number format-- Date/Time-- Here select the format how you want to see...

Basically,

Getdate() from SQL Server not carrying timestamp.

And WebI report level CurrentDate() also.

Thank You!!

Former Member
0 Kudos

looking like it is a format issue in the report. in the report, what is the format for the objects. make sure it should be number.

in the report, right click on the object and format and change it to number.

Former Member
0 Kudos

Hi Sreedhar & BOCP-BE,

Sorry to mention you before the Age Days is calculating the Days, I used

Age Days >>> DATEDIFF(dd,CLARITY.dbo.EMPLOYEE.BIRTH_DATE,GETDATE())

Now I am working on the Years, Please help me out

Employee Age Years >>> truncate ((CurrentDate - {EMPLOYEE.BIRTH_DATE}) / 365)

Please correct me if I am wrong, for truncate I tried using floor() or round(,). But in this scenario I am dealing with Years.

Thanks

R

Former Member
0 Kudos

First of all, Are you doing at Universe or Report??

Formula/Variable :

Obj1: Extract(YEAR from CurrentDate)

Obj2: Extract(YEAR from EMPLOYEE.BIRTH_DATE)

Obj3: (Obj1-Obj2)/365

Oracle I mentioned, for SQL Server am not aware.

Hope this helps you.

DATEADD(Year, DATEDIFF(Year, 0, GetDate()), 0)

Thank You!!

Former Member
0 Kudos

I am doing at Universe level and thanks for the code. I will try and use.

Former Member
0 Kudos

No luck.

Former Member
0 Kudos

Hey R,

Can you see Year part at report or not??

Your formula would help me a lot.

Thank You!!