Convert Date field to Numeric Field

building semantic layer
1

Hi,

I have a date filed in my universe called Due Date which displays in this format MM/DD/YYYY. Is there a way to convert that date field into this format YYYYMMDD as a numeric field. We are planning to link this object with other universes where as all the renaming universes Due Date filed are in this format YYYYMMDD in numeric format. So i want to convert this object also into Numeric field and link with it.

Thanks
Raju

Rajua99 :india: (BOB member since 2004-12-16)

2

What is your database?

You may try something like below if it is Oracle
select to_number(to_char(column_name,‘YYYYMMDD’)) from table_name

bo_mate :australia: (BOB member since 2004-06-16)

3

If you’re using SQL Server, the syntax is very different.

cast(replace(CONVERT(char(10),getdate(),23),'-','') as int)

That will give you today’s date as an integer (yyyymmdd format) so simply swap getdate() out for your database column name.

Regards,
Mark

4

My database is AS400. Not Oracle or SQL Server.

Rajua99 :india: (BOB member since 2004-12-16)