How to get proper date value from a number?

[#1]

Dear All,

I'm new to QlikView.

I have got my data in MS SQL database.

The DateX column is stored as the number of days since 28/12/1800.

To questions:

1. How to get to the proper date value in QlikView for a number 77070 (I know it should be 01/01/2012.

2. Is it possible to use in QlikView script MS SQL Scalar-valued function which does the conversion to proper date. (Function takes the DateX column as an argument  and returns varchar(10) string).

Thanks in advance for your contribution.

Regards

Adam

[MayilVahanan]

HI,

     Try like this,

     =Date(77070,'DD/MM/YYYY')

     Hope it helps

[#3]

Hi Mayil

When I have used it in QV script as SQL Select element I have got an error:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name.

More ideas?

Regards

Adam

[MayilVahanan]

HI,

     Load *, Date(FieldName,'DD/MM/YYYY') as DateField;

     Select * from TableName;

    Hope it helps

[#5]

No sccess.

Instead giving me date 01/06/2012 it gives me 04/06/2111.

That means Date functions works somehow, but not as I expect.

Regards

Adam

[swuehl]

I think there should be something more elegant to get the correct date from your MS SQL server, but if you only get a number back, you need to subtract the difference in days of the calendar origins (30.12.1899 - 28.12.1800), which is

36161.

So, try subtracting this number from your date field:

=date(77070-36161)

resp

LOAD

Date(DATEFIELD - 36161) as Date,

...

FROM ...;

[#7]

Hi Swuehl!

And your elegant way works great.

Thanks a lot.

But I'm still looking for an answer for the second question:

Is it possible to use MS SQL Scalar-valued function in QlikView script? If yes, how to do it?

Regards

Adam