topic Re: How to get proper date value from a number? in QlikView

How to get proper date value from a number?

Thu, 26 Jul 2012 14:31:17 GMT

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

Re: How to get proper date value from a number?

MayilVahanan — Thu, 26 Jul 2012 14:39:32 GMT

HI,

Try like this,

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

Hope it helps

Re: How to get proper date value from a number?

Thu, 26 Jul 2012 15:05:16 GMT

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

Re: How to get proper date value from a number?

MayilVahanan — Thu, 26 Jul 2012 16:17:19 GMT

HI,

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

Select * from TableName;

Hope it helps

Re: How to get proper date value from a number?

Fri, 27 Jul 2012 15:03:53 GMT

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

Re: How to get proper date value from a number?

swuehl — Fri, 27 Jul 2012 15:35:42 GMT

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 ...;

Re: How to get proper date value from a number?

Mon, 30 Jul 2012 15:16:31 GMT

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